How to split parent/child column in Qlik Sense?

[Dan17]

Howdy folks,

I have a table that looks like this:

Id Type
13 Level1
1  Level2
2  Level3
3  Level3
4  Level2
5  Level3
42 Level1
6  Level2
10 Level1
8  Level2
9  Level3

(The indentation isn't really there I just wanted to highlight the parent-child relations in the data)
What I want to do is create the parent-child relations in the load script, so that I get something like this:

Id  Type     Level1 ID  Level2 ID
13  Level1   13
1   Level2   13          1
2   Level3   13          1
3   Level3   13          1
4   Level2   13          4
5   Level3   13          4
42  Level1   42
6   Level2   42          6
10  Level1   10
8   Level2   10          8
9   Level3   10          8

Now after 2 days of research I can't figure out how to do that. It seems simple, but most functions like hierarchy() work only when the parent-child relationship already exists in separate columns. 

Does anyone have an idea how to do that? 

Best Regards,

Daniel

[vinieme12]

You can use Hierarchy or HierarchybelongsTo;

• Hierarchy
• HierarchyBelongsTo

[rubenmarin · Accepted solution]

Hi, to use one of the hierarchy functions you first need to have each parent id informed in each node and you don't have that field, it the hierarchy is based on load order (Each node is dependant of the previos with lower level, you can add the parentId with:

TranformData:
LOAD
  Id,
  Type,
  If(Type='Level1', Id, Peek('Level1 ID')) as [Level1 ID],
  If(Type='Level2', Id, Peek('Level2 ID')) as [Level2 ID],
  If(Type='Level3', Id, Peek('Level3 ID')) as [Level3 ID],
  If(Type='Level1'
    ,Null()
    ,If(Type=Peek('Type')
      ,Peek('ParentId')
      ,If(Type='Level2', Peek('Level1 ID')
      ,If(Type='Level3', Peek('Level2 ID')
   )))) as ParentId
Resident OrigData;

Or you can switch some o f the script to directly return the table in the [Level1 ID], [Level2 ID] and [Level3 ID] fields, it just needs a couple of "if's" to store the dte you want.

[MarcoWedel]

another solution might be:

table1:
Hierarchy (Id, ParentId, Level)
LOAD *,
     Id as Level,
     Text(Left(Peek(Path)&'/',Index(Peek(Path)&'/','/',LevelNo-1))&Id) as Path,
     SubField(Peek(Path),'/',LevelNo-1) as ParentId;
LOAD *, SubField(Type,'Level',2) as LevelNo
Inline [
Id, Type
13, Level1
1,  Level2
2,  Level3
3,  Level3
4,  Level2
5,  Level3
42, Level1
6,  Level2
10, Level1
8,  Level2
9,  Level3
11, Level3
12, Level4
14, Level4
15, Level5
16, Level3
17, Level4
18, Level4
19, Level5
20, Level4
21, Level5
];

hope this helps

Marco

[Dan17 · Accepted solution]

Well, that did the job. Thanks. Actually tried this approach before, but the way I did it was not Qlik Sense grammar compatible. 

[Dan17]

Hello,

the problem with that solution is that when next Level1 item is read you get:

Id    Type        Level1 ID    Level2 ID

42    Level1    42                  <last read Level2 ID>

instead of 

Id    Type        Level1 ID    Level2 ID

42    Level1    42                  -

So far I have not been able to find a way to get rid of the previously loaded field values. 

[rubenmarin]

Hi, that's the part about "Or you can switch some o f the script to directly return the table in the [Level1 ID], [Level2 ID] and [Level3 ID] fields, it just needs a couple of "if's" to store the dte you want."

Have you checked @MarcoWedel solution? It seems to work better.

[Dan17]

Well, I don't see what you mean by "Or you...".

I haven't looked into the other solution yet, but I should have. It would require pre and postprocessing of my input data to make the algorithm work. 

[rubenmarin]

One option could bu adding a hiercachy function:

TranformData:
Hierarchy (Id, ParentId, Level)
LOAD
  Id,
  Id as Level,
  Type,
  If(Type='Level1', Id, Peek('Level1 ID')) as [Level1 ID],
  If(Type='Level2', Id, Peek('Level2 ID')) as [Level2 ID],
  If(Type='Level3', Id, Peek('Level3 ID')) as [Level3 ID],
  If(Type='Level1'
    ,Null()
    ,If(Type=Peek('Type')
      ,Peek('ParentId')
      ,If(Type='Level2', Peek('Level1 ID')
      ,If(Type='Level3', Peek('Level2 ID')
   )))) as ParentId
Resident OrigData;

The option with additional If's could be:

TranformData:
LOAD
  Id,
  Type,
  If(Type='Level1', Id, Peek('Level1 ID')) as [Level1 ID],
  If(Type='Level2', Id, If(Type<>'Level1',Peek('Level2 ID'))) as [Level2 ID],
  If(Type='Level3', Id, If(not Match(Type,'Level1','Level2'),Peek('Level3 ID'))) as [Level3 ID],
  If(Type='Level1'
    ,Null()
    ,If(Type=Peek('Type')
      ,Peek('ParentId')
      ,If(Type='Level2', Peek('Level1 ID')
      ,If(Type='Level3', Peek('Level2 ID')
   )))) as ParentId
Resident OrigData;

But again, this is limited to 3 levels, Marco's solution is more dynamic.