Hierarchy

[dhavalvyas]

Hi,

I am trying to develop hierarchy in one of the application.The hierarchy user want is,

level ->positiontype->employeeid

In hierarchy we can enter just field name,but in my application there is seprate fields for each level i.e. for level 0 different field,level 1 different field.so how it is possible to do hierarchy ?

Sample example raw data I have attached as below.

As you can see ion raw data i attached below you will get idea for level fields.

[jaume_soc]

Try unpivoting the table:

https://help.qlik.com/en-US/sense/3.2/Subsystems/Hub/Content/LoadData/work-with-cross-tables.htm

I think this lik might help you. In the end you have to transform that so that you get something like this:

employeeid	positiontype	level	(value)
1	e1	0	1345
1	e1	1	3445
1	e1	2	8754
1	e1	3	2345
1	e1	4	6564
1	e1	5	5647
2	e2	0	2321
2	e2	1	6754
2	e2	2	6787
2	e2	3	1345
2	e2	4	1345
2	e2	5	1234

....

you have another field (level) which contains the other 6 fields (level 0 to 5)

[hari8088]

use below script or find the attached qvw.

Data:

CrossTable(Levels,Data,2)

LOAD employeeid, positiontype, level0, level1, level2, level3, level4, level5

FROM (ooxml, embedded labels, table is Sheet1);

Hierarcy:

LOAD Levels&'_levels' as value,Levels&'_levels' as nodeid,Levels&'_levels' as parentid Resident Data;

LOAD positiontype&'_positiontype' as value,positiontype&'_positiontype' as nodeid,Levels&'_levels' as parentid Resident Data;

LOAD employeeid&'_employeeid' as value,employeeid&'_employeeid' as nodeid,positiontype&'_positiontype' as parentid Resident Data;

final:

Hierarchy(nodeid,Newparentid,id,parentidnew,name,structure)

LOAD

value as id,

value as name,

nodeid,

nodeid as newnodeid,

parentid as Newparentid

Resident Hierarcy;

DROP Table Hierarcy;

DROP Table Data;