Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi there
I have a situation where I have a tree structure which can be simplified to something matching the table below:
NodeName | NodeId | NodeParentId |
A | 0 | |
A1 | 1 | 0 |
A2 | 2 | 0 |
A21 | 3 | 2 |
If I had to draw it, it would look as follows:
I would like to get it in a format where I will be able to see the node itself and all of the sub-nodes of each node in a table like the one below:
Node | ConnectedNode |
A | A |
A | A1 |
A | A2 |
A | A21 |
A1 | A1 |
A2 | A2 |
A2 | A21 |
A21 | A21 |
Using the Hierarchy function I am able to get the following output:
NodeName | NodeName1 | NodeName2 | NodeName3 |
A | A | - | - |
A1 | A | A1 | - |
A2 | A | A2 | - |
A21 | A | A2 | A21 |
I am a bit stuck on how to get there. I think CrossTable will help, but I am not sure how to get there.
The reason why I need this is because I need to link events to these nodes and measures for each node need to be created using the links on that node and in some cases the links on the node as well as the nodes below it.
PS. My tree can have a range of levels, not just three like the one above so it needs to be a dynamic solution.
Please let me know if anything is unclear.
Regards,
Mauritz
Hi Zhandos
Thanks for the reply. I tried your suggestion and got a bit stuck, but ended up doing something similar using LevelNo which worked. Below my script as a reference:
[Nodes]:
Hierarchy(NodeId, NodeParentId, NodeName,,,,,LevelNo) //Hierarchy(‹Node Id›, ‹Parent Id›, ‹Node Name›, ‹Parent Name›, ‹Path Source›, ‹Path Name›, ‹Path Delimiter›, ‹Hierarchy Depth›)
LOAD
[AssetName] AS NodeName,
[AssetId] AS NodeId,
[AssetParentId] AS NodeParentId
FROM [lib://Desktop/TreeTest.xlsx]
(ooxml, embedded labels, table is Assets);
Levels:
Load
MAX(LevelNo) AS MaxLevel
RESIDENT [Nodes];
LET vMaxLevel = Peek('MaxLevel', 0, 'Levels');//peek('MaxLevel');
DROP TABLE Levels;
DROP FIELDS NodeId,NodeParentId,LevelNo;
TRACE vMaxLevel = $(vMaxLevel);
For vLevel = 1 to vMaxLevel
Let vColumn = 'NodeName'&'$(vLevel)';
Trace vColumn = $(vColumn);
TreeAndNodes:
LOAD
NodeName AS ConnectedNode,
$(vColumn) AS Node
Resident Nodes
WHERE
Len($(vColumn)) > 0
;
Next vLevel
DROP TABLE Nodes;
Basically it just loops through my Hierarchy columns and creates the Node and ConnectedNode fields.
Thanks for the help.
Regards,
Mauritz
Hi!
Interesting..
Here some thoughts:
1. In Hierarchy load add LevelNo field
2. Then try something like
Join
NodeName1, (will be Node)
NodeName$(vLevelNo) as ConnectedNode;
OR
Load
NodeName1 & '|' & NodeName$(vLevelNo) as ConcatenatedFields;
and load again with SubField(ConcatenatedFields, '|', 2) as ConnectedNode
Hope it helps!
Hi Zhandos
Thanks for the reply. I tried your suggestion and got a bit stuck, but ended up doing something similar using LevelNo which worked. Below my script as a reference:
[Nodes]:
Hierarchy(NodeId, NodeParentId, NodeName,,,,,LevelNo) //Hierarchy(‹Node Id›, ‹Parent Id›, ‹Node Name›, ‹Parent Name›, ‹Path Source›, ‹Path Name›, ‹Path Delimiter›, ‹Hierarchy Depth›)
LOAD
[AssetName] AS NodeName,
[AssetId] AS NodeId,
[AssetParentId] AS NodeParentId
FROM [lib://Desktop/TreeTest.xlsx]
(ooxml, embedded labels, table is Assets);
Levels:
Load
MAX(LevelNo) AS MaxLevel
RESIDENT [Nodes];
LET vMaxLevel = Peek('MaxLevel', 0, 'Levels');//peek('MaxLevel');
DROP TABLE Levels;
DROP FIELDS NodeId,NodeParentId,LevelNo;
TRACE vMaxLevel = $(vMaxLevel);
For vLevel = 1 to vMaxLevel
Let vColumn = 'NodeName'&'$(vLevel)';
Trace vColumn = $(vColumn);
TreeAndNodes:
LOAD
NodeName AS ConnectedNode,
$(vColumn) AS Node
Resident Nodes
WHERE
Len($(vColumn)) > 0
;
Next vLevel
DROP TABLE Nodes;
Basically it just loops through my Hierarchy columns and creates the Node and ConnectedNode fields.
Thanks for the help.
Regards,
Mauritz