Skip to main content
Announcements
Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL: Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
qlikconsultant
Creator III
Creator III

Transform of a hierarchy table

Hi,

I have the following hierarchy  table

Person from to
Person A   1000
Person B 100 1000
Person C 10 100

and I need it to transform it to 

Person  
Person A 1000
Person A 100
Person A 10
Person B 100
Person B 10
Person C 10

 

What is the best way to do it?

Labels (1)
4 Replies
edwin
Master II
Master II

just by looking at it, i cant get the business rule.  you may need to add more info

qlikconsultant
Creator III
Creator III
Author

Basically, Person A has access to account 1000 with that he has also access to all sub accounts 100 and to all sub sub accounts for example 10.

Person B has only access to sub account 100 and with that access also to 10.

Person C has only access 10.

Goal is a list for every Person to see all her accounts.

marcus_sommer

I assume that your description is further a bit too simplified and the aim is more to get all accounts between 1 and 1000 and not only the shown 10 / 100 / 1000. This would be a common use-case of resolving numeric ranges to dedicated values and might be done with an IntervalMatch - Qlik Community - 1464547 as extra dimension-table or merged with the source-table.

IMO simpler and more powerful would be to resolve it with an internal while loop, like:

load Person, rangemax(1, from) + iterno() - 1 as Account
from Source while rangemax(1, from) + iterno() - 1 <= to;

Should be the goal to end really by only 3 accounts / respectively certain ones you may extend the above example with a step-field which is then included to the iterno()-iteration and/or adding where-clauses within a preceding-load.

Kushal_Chawda

@qlikconsultant  one more approach could be

Data:
Load * Inline [
Person, from, to
Person A,, 1000
Person B, 100, 1000
Person C, 10, 100 ];

Values:
Load num(FieldValue('from',RecNo())) as Values
AutoGenerate FieldValueCount('from');

Load num(FieldValue('to',RecNo())) as Values
AutoGenerate FieldValueCount('to');

Left Join(Data)
Load Distinct Values
Resident Values
where len(Trim(Values))>0;

Drop Table Values;

Final:
NoConcatenate
Load *
Resident Data
where trim(Len(from))=0 or Values<=from;

Drop Table Data;