Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
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;