Transform of a hierarchy table

[qlikconsultant]

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?

[edwin]

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

[qlikconsultant]

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;