Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
Mauritz_SA
Contributor III

Cross table help (I think)

Hi there

I have a situation where I have a tree structure which can be simplified to something matching the table below:

NodeNameNodeIdNodeParentId
A0 
A110
A220
A2132

 

If I had to draw it, it would look as follows:

Simple Tree.PNG

 

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:

NodeConnectedNode
AA
AA1
AA2
AA21
A1A1
A2A2
A2A21
A21A21

 

Using the Hierarchy function I am able to get the following output:

NodeNameNodeName1NodeName2NodeName3
AA--
A1AA1-
A2AA2-
A21AA2A21

 

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

Labels (3)
1 Solution

Accepted Solutions
Mauritz_SA
Contributor III

Re: Cross table help (I think)

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

 

2 Replies
Zhandos_Shotan
Contributor

Re: Cross table help (I think)

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
Contributor III

Re: Cross table help (I think)

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