QLIKSENSE - Hierarchy - Find Father!

[Cyril_63]

Dear Qlik Users,
I'm facing since 1 week an issue concerning link between father and son.
What I want to do ? Create a hierarchy but before I should to find the father of each son.
but how to proceed ?

LEVEL 0 is the highest “father” level. The father of level N is the last son of level N-1

Below an example of data (source) :
LEVEL, CODE
0, CODE0
1, CODE1
1, CODE2
2, CODE3
2, CODE4
2, CODE5
3, CODE6
3, CODE7
3, CODE8
3, CODE9
4, CODE10
4, CODE11
4, CODE12
3, CODE13
2, CODE14
2, CODE15
2, CODE16
3, CODE17
4, CODE18
5, CODE19
3, CODE20
4, CODE21
1, CODE22
2, CODE23

Could you help me? If you have ever done this type of job or if you know some posts it could be great...

Thanks a lot

key words : recurcivity, hierarchy, Father and Son

[Aurelien_Martinez]

Hi,

With the fonction Hierachy or HierachyBelongTo

Hierarchy(LEVEL, FATHER, CODE)
Load
LEVEL,
CODE,
If(LEVEL = 0, 0, LEVEL - 1) as FATHER
Inline [
LEVEL, CODE
0, CODE0
1, CODE1
1, CODE2
2, CODE3
2, CODE4
2, CODE5
3, CODE6
3, CODE7
3, CODE8
3, CODE9
4, CODE10
4, CODE11
4, CODE12
3, CODE13
2, CODE14
2, CODE15
2, CODE16
3, CODE17
4, CODE18
5, CODE19
3, CODE20
4, CODE21
1, CODE22
2, CODE23
];

Aurélien

[Cyril_63]

Hi Aurelien,

Thank you for your proposal solution. But it's not that simple.

The LEVEL is not an unique ID. The LEVEL explains a level on the hierarchy. For example :

CODE1 and CODE2 are sons of CODE0, 

CODE3, CODE4 and CODE5 are sons of CODE2

...

CODE22 is son of CODE0

The previous level is not necessarily the father

[marcus_sommer]

This should be helpful: Hierarchies - Qlik Community - 1487801

[Cyril_63]

Good afternoon Marcus_sommer,

Thank you for the link, I've seen it. It explains very well "how to use" Hierarchy function. However, the “Parent_ID” field is missing from my data source. This is the subject of this message. How do I create this missing column...?

Regards

Cyril

[marcus_sommer]

I think the suggested Level - 1 approach goes already in the right direction. If the Level isn't unique you need to add more logic, maybe by wrapping it into n (nested) if-loops - maybe by evaluating the load-order. This might be done within a sorted resident-load and interrecord-functions like previous() and peek().

Further thinkable may the use of exists(), fieldvalue() or lookup() - but it will need a clear logic to the existing data to get valid results respectively you may need more relevant information like a creation/change timestamp or something else. Or in short: the raw-information must be within the data to be able to derive any extending-data. 

[Cyril_63 · Accepted solution]

Hello everyone,
After hours and hours of patience and trying, I've found a solution in attached file.

As I'm not a Qlik script expert, please bear with me.

I hope it helps someone. Feel free to use and modify it.