Cross table help (I think)

[Mauritz_SA]

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

[Zhandos_Shotan]

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!

[Mauritz_SA · Accepted solution]

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