如何在vega-lite中执行对同一数据集的查找

huangapple
go评论62阅读模式
英文:

How to perform a lookup to same dataset in vega-lite

问题

I'm using vega-lite 5.6.1 within the Deneb visual for Power BI. I've built a tree/matrix hybrid visual:
如何在vega-lite中执行对同一数据集的查找

Now I need to get child rows to dynamically lookup fields from their parent row. This needs to happen after the dataset has been filtered so I can allow users to collapse and expand rows.

I've tried using the Lookup Transform but can't pass in the "from" parameter since it expects a secondary dataset:

{
  "data": {"name": "dataset"},
  "transform": [
    ...,
    {"filter": ... },
    {"lookup": "Parent_Path", "from": ... , "key": "Child_Path", "fields": ["Horiz_Offset", "Vert_Offset"], "as": "Parent_Row"}
  ],
  "layer": [
    ...
  ]
}

Out of desperation I experimented using the JoinAggregate Transform but it doesn't allow expressions in the "field" parameter to filter it:

{
  "data": {"name": "dataset"},
  "transform": [
    ...,
    {"filter": ... },
    {"joinaggregate": [{"op": "min", "field": {"value": {"expr": "child.Parent_Path == parent.Child_Path ? Horiz_Offset : null"}}, "as": "Parent_Horiz_Offset"}]}
  ],
  "layer": [
    ...
  ]
}

Here's an example containing a hardcoded dataset. Normally the data would be passed from an external dataset as: "data": {"name": "dataset"}. Instead of passing in Parent_Horiz_Offset and Parent_Vert_Offset I want to get the values from their parent row:

{
  "data": {
    "values": [
      {"Full_Path": "Level 1a\\Level 2a\\Level 3a\\Employee 01", "Child_Path": "Level 1a", "Parent_Path": "", "Node_Text": "Level 1a", "Horiz_Offset": 0, "Vert_Offset": 0, "Parent_Horiz_Offset": null, "Parent_Vert_Offset": null},
      {"Full_Path": "Level 1a\\Level 2a\\Level 3a\\Employee 01", "Child_Path": "Level 1a\\Level 2a", "Parent_Path": "Level 1a", "Node_Text": "Level 2 a", "Horiz_Offset": 1, "Vert_Offset": 1, "Parent_Horiz_Offset": 0, "Parent_Vert_Offset": 0},
      {"Full_Path": "Level 1a\\Level 2a\\Level 3a\\Employee 01", "Child_Path": "Level 1a\\Level 2a\\Level 3a", "Parent_Path": "Level 1a\\Level 2a", "Node_Text": "Level 3a", "Horiz_Offset": 2, "Vert_Offset": 2, "Parent_Horiz_Offset": 1, "Parent_Vert_Offset": 1},
      {"Full_Path": "Level 1a\\Level 2a\\Level 3a\\Employee 01", "Child_Path": "Level 1a\\Level 2a\\Level 3a\\Employee 01", "Parent_Path": "Level 1a\\Level 2a\\Level 3a", "Node_Text": "Employee 01", "Horiz_Offset": 3, "Vert_Offset": 3, "Parent_Horiz_Offset": 2, "Parent_Vert_Offset": 2}
    ] 
  },
  "params": [
    {"name": "_indentSize", "value": 30},
    {"name": "_iconSize", "value": 80},
    {"name": "_rowHeight", "value": 15},
    {"name": "_headerRowHeight", "value": 0}
  ],
  "transform": [
    {"window": [{"op": "dense_rank", "as": "_rowNum"}], "ignorePeers": true, "sort": [{"field": "Parent_Path", "order": "ascending"}]},
    {"window": [{"op": "row_number", "as": "_index"}], "groupby": ["Child_Path"]},
    {"calculate": "datum.Horiz_Offset * _indentSize", "as": "_xPos"},
    {"calculate": "datum.Vert_Offset * _rowHeight", "as": "_yPos"},
    {"calculate": "datum.Parent_Horiz_Offset * _indentSize", "as": "_xPos_Parent"},
    {"calculate": "datum.Parent_Vert_Offset * _rowHeight", "as": "_yPos_Parent"},
    {"calculate": "datum.Full_Path == datum.Child_Path", "as": "_isLeafNode"},
    {"calculate": "datum.Parent_Path == ''", "as": "_isRootNode"},
    {"filter": "datum._index == 1"}
  ],
  "layer": [
    {
      "description": "Row Background",
      "params": [{"name": "isSelected", "select": "point"}],
      "mark": {"type": "rect", "stroke": "#E1E2E3", "cursor": "pointer"},
      "encoding": {
        "x": {"value": {"expr": "datum._xPos - 8"}},
        "y": {"value": {"expr": "datum._yPos - ceil(_rowHeight / 2)"}},
        "x2": {"value": {"expr": "320"}},
        "y2": {"value": {"expr": "datum._yPos + floor(_rowHeight / 2)"}},
        "color": {"value": {"expr": "datum._yPos % 2 == 0 ? 'white' : '#F1F2F3'"}}
      }
    },
    {
      "description": "Node line horiz",
      "mark": {"type": "rule", "color": "#00002D"},
      "encoding": {
        "x": {"value": {"expr": "datum._xPos_Parent"}},
        "x2": {"value": {"expr": "datum._xPos"}},
        "y": {"value": {"expr": "datum._yPos"}},
        "y2": {"value": {"expr": "datum._yPos"}},
        "opacity": {"value": {"expr": "datum._isRootNode ? 0 : 1"}}
      }
    },
    {
      "description": "Node line vert",
      "mark": {"type": "rule", "color": "#00002D"},
      "encoding": {
        "x": {"value": {"expr": "datum._xPos_Parent"}},
        "x2": {"value": {"expr": "datum._xPos_Parent"}},
        "y": {"value": {"expr": "datum._yPos_Parent"}},
        "y2": {"value": {"expr": "datum._yPos"}},
        "opacity": {"value": {"expr": "datum._isRootNode ? 0 : 1"}}
      }
    },


<details>
<summary>英文:</summary>

I&#39;m using vega-lite 5.6.1 within the Deneb visual for Power BI.  I&#39;ve built a tree/matrix hybrid visual:
[![enter image description here](https://i.stack.imgur.com/qSQ6U.png)](https://i.stack.imgur.com/qSQ6U.png)

Now I need to get child rows to dynamically lookup fields from their parent row.  This needs to happen after the dataset has been filtered so I can allow users to collapse and expand rows.

I&#39;ve tried using the Lookup Transform but can&#39;t pass in the `&quot;from&quot;:` parameter since it expects a secondary dataset:

{
"data": {"name": "dataset"},
"transform": [
...,
{"filter": ... },
{"lookup": "Parent_Path", "from": ... , "key": "Child_Path", "fields": ["Horiz_Offset", "Vert_Offset"], "as": "Parent_Row"}
],
"layer": [
...
]
}


Out of desperation I experimented using the JoinAggregate Transform but it doesn&#39;t allow expressions in the `&quot;field&quot;:` parameter to filter it:

{
"data": {"name": "dataset"},
"transform": [
...,
{"filter": ... },
{"joinaggregate": [{"op": "min", "field": {"value": {"expr": "child.Parent_Path == parent.Child_Path ? Horiz_Offset : null"}}, "as": "Parent_Horiz_Offset"]}
],
"layer": [
...
]
}


Here&#39;s an example containing a hardcoded dataset.  Normally the data would be passed from an external dataset as: `&quot;data&quot;: {&quot;name&quot;: &quot;dataset&quot;}`.  Instead of passing in _Parent_Horiz_Offset_ and _Parent_Vert_Offset_ I want to get the values from their parent row.

{
"data": {
"values": [
{"Full_Path": "Level 1a\Level 2a\Level 3a\Employee 01", "Child_Path": "Level 1a", "Parent_Path": "", "Node_Text": "Level 1a", "Horiz_Offset": 0, "Vert_Offset": 0, "Parent_Horiz_Offset": null, "Parent_Vert_Offset": null},
{"Full_Path": "Level 1a\Level 2a\Level 3a\Employee 01", "Child_Path": "Level 1a\Level 2a", "Parent_Path": "Level 1a", "Node_Text": "Level 2 a", "Horiz_Offset": 1, "Vert_Offset": 1, "Parent_Horiz_Offset": 0, "Parent_Vert_Offset": 0},
{"Full_Path": "Level 1a\Level 2a\Level 3a\Employee 01", "Child_Path": "Level 1a\Level 2a\Level 3a", "Parent_Path": "Level 1a\Level 2a", "Node_Text": "Level 3a", "Horiz_Offset": 2, "Vert_Offset": 2, "Parent_Horiz_Offset": 1, "Parent_Vert_Offset": 1},
{"Full_Path": "Level 1a\Level 2a\Level 3a\Employee 01", "Child_Path": "Level 1a\Level 2a\Level 3a\Employee 01", "Parent_Path": "Level 1a\Level 2a\Level 3a", "Node_Text": "Employee 01", "Horiz_Offset": 3, "Vert_Offset": 3, "Parent_Horiz_Offset": 2, "Parent_Vert_Offset": 2}
]
},
"params": [
{"name": "_indentSize", "value": 30},
{"name": "_iconSize", "value": 80},
{"name": "_rowHeight", "value": 15},
{"name": "_headerRowHeight", "value": 0}
],
"transform": [
{"window": [{"op": "dense_rank", "as": "_rowNum"}], "ignorePeers": true, "sort": [{"field": "Parent_Path", "order": "ascending"}]},
{"window": [{"op": "row_number", "as": "_index"}], "groupby": ["Child_Path"]},
{"calculate": "datum.Horiz_Offset * _indentSize", "as": "_xPos"},
{"calculate": "datum.Vert_Offset * _rowHeight", "as": "_yPos"},
{"calculate": "datum.Parent_Horiz_Offset * _indentSize", "as": "_xPos_Parent"},
{"calculate": "datum.Parent_Vert_Offset * _rowHeight", "as": "_yPos_Parent"},
{"calculate": "datum.Full_Path == datum.Child_Path", "as": "_isLeafNode"},
{"calculate": "datum.Parent_Path == ''", "as": "_isRootNode"},
{"filter": "datum._index == 1"}
],
"layer": [
{
"description": "Row Background",
"params": [{"name": "isSelected", "select": "point"}],
"mark": {"type": "rect", "stroke": "#E1E2E3", "cursor": "pointer"},
"encoding": {
"x": {"value": {"expr": "datum._xPos - 8"}},
"y": {"value": {"expr": "datum._yPos - ceil(_rowHeight / 2)"}},
"x2": {"value": {"expr": "320"}},
"y2": {"value": {"expr": "datum._yPos + floor(_rowHeight / 2)"}},
"color": {"value": {"expr": "datum._yPos % 2 == 0 ? 'white' : '#F1F2F3'"}},
"opacity": {"condition": {"param": "isSelected", "value": 1}, "value": 0.5}
}
},
{
"description": "Node line horiz",
"mark": {"type": "rule", "color": "#00002D"},
"encoding": {
"x": {"value": {"expr": "datum._xPos_Parent"}},
"x2": {"value": {"expr": "datum._xPos"}},
"y": {"value": {"expr": "datum._yPos"}},
"y2": {"value": {"expr": "datum._yPos"}},
"opacity": {"value": {"expr": "datum._isRootNode ? 0 : 1"}}
}
},
{
"description": "Node line vert",
"mark": {"type": "rule", "color": "#00002D"},
"encoding": {
"x": {"value": {"expr": "datum._xPos_Parent"}},
"x2": {"value": {"expr": "datum._xPos_Parent"}},
"y": {"value": {"expr": "datum._yPos_Parent"}},
"y2": {"value": {"expr": "datum._yPos"}},
"opacity": {"value": {"expr": "datum._isRootNode ? 0 : 1"}}
}
},
{
"description": "Node icon",
"mark": {"type": "point"},
"encoding": {
"x": {"value": {"expr": "datum._xPos"}},
"y": {"value": {"expr": "datum._yPos"}},
"shape": {"value": {"expr": "datum._isLeafNode ? 'circle' : 'square'"}}
}
},
{
"description": "Node text",
"mark": {"type": "text"},
"encoding": {
"x": {"value": {"expr": "datum._xPos + 10"}},
"y": {"value": {"expr": "datum._yPos"}},
"text": {"field": "Node_Text"},
"opacity": {"condition": {"param": "isSelected", "value": 1}, "value": 0.5}
}
}
],
"config": {
"text": {
"fill": "#00002D",
"align": "left",
"font": "Arial",
"fontSize": 10
},
"point": {
"size": 80,
"opacity": 1
}
}
}



</details>


# 答案1
**得分**: 1

Nice visual. You can pass the name into the lookup transform to get what you need. e.g.

{
  "lookup": "Full_Path",
  "from": {
    "data": {"name": "myDataset"},
    "key": "Full_Path",
    "fields": ["Node_Text"]
  },
  "as": ["test"]
}

<details>
<summary>英文:</summary>

Nice visual. You can pass the name into the lookup transform to get what you need. e.g. 

    {
      &quot;lookup&quot;: &quot;Full_Path&quot;,
      &quot;from&quot;: {
        &quot;data&quot;: {&quot;name&quot;: &quot;myDataset&quot;},
        &quot;key&quot;: &quot;Full_Path&quot;,
        &quot;fields&quot;: [&quot;Node_Text&quot;]
      },
      &quot;as&quot;: [&quot;test&quot;]
    }

</details>

huangapple
  • 本文由 发表于 2023年5月25日 11:35:17
  • 转载请务必保留本文链接：https://go.coder-hub.com/76328763.html
  • deneb
  • powerbi
  • vega-lite
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定