Flatten Hierarchy

[Chris_Elders_VZ]

Essentially what I am trying to do is take the hierarchy from a table that has been flattened out, create a Member/Parent table so that when I make either an update/add/move within the hierarchy, I can replace the current hierarchy so I can see current vs a future state.  

I successfully created the qvd for the Member/Parent table, and (see sample script below by concatenation of 8 levels) but when we try to reflatten it out by using the Hierarchy formula in the help documentation, either I have not included enough or I am doing it wrong. Below is the formula from the Help section followed by my attempt at it. If I use my formula even after an hour and 15 minutes it has not completed.

Am I missing something? Is there a better way? I would greatly appreciate anybody’s suggestions.

Thank you,

Chris

Example of Member/Parent script:

Load
HIER_3_KEY AS MEMBER,
HIER_3_DESC AS "MEMBER DESC",
HIER_2_KEY AS PARENT,
HIER_2_DESC AS "PARENT DESC",
'LEVEL 3' AS "MEMBER LEVEL",
'LEVEL 2' AS "PARENT LEVEL",
'1' AS "APPLY ORDER"
FROM
[lib://folder1/folder2/GPC_CODE_MASTER_VW.qvd] (QVD)
WHERE NOT ISNULL (HIER_3_KEY);
Concatenate

Load
HIER_2_KEY AS MEMBER,
HIER_2_DESC AS "MEMBER DESC",
HIER_1_KEY AS PARENT,
HIER_1_DESC AS "PARENT DESC",
'LEVEL 2' AS "MEMBER LEVEL",
'LEVEL 1' AS "PARENT LEVEL",
'1' AS "APPLY ORDER"
FROM
[lib://folder1/folder2/GPC_CODE_MASTER_VW.qvd] (QVD)
WHERE NOT ISNULL (HIER_2_KEY);

Qlik Help formula:

Hierarchy (NodeID, ParentID, NodeName, [ParentName], [PathSource], [PathName], [PathDelimiter], [Depth])(loadstatement | selectstatement)

My attempt at creating formula:

Hierarchy (MEMBER, PARENT, "MEMBER DESC") LOAD
MEMBER,
PARENT,
"MEMBER DESC"

FROM [lib://folder1/folder2/MDM_MEMBER_PARENT_FACT.QVD]
(qvd);

[ArnadoSandoval · Accepted solution]

Hi @Chris_Elders_VZ 

It will be good to include volume (number of records) when reporting a performance issue, as in you indicated your Hierarchy statement is taking 1 hour and 15 minutes !!! It is also good to share an screenshot of your data; it helps the helpers, anyhow, it worked for me in a flash, obviously I was unable to test with 3 millions records, just I mocked a data sample preserving your columns names while using continents-countries-state-city hierarchy up to four levels.

I referenced these articles:

• QlikSense Hierarchy 
• Qlik Community thread regarding Hierarchy 

This is the mock-up data inserted into the headers you provided.

This is the Hierarchy table produced.

This is the load script:

NoConcatenate
Source_Data:
LOAD
    HIER_3_KEY  As Member,
    HIER_3_DESC As NodeName, // Member_Desc,
    HIER_2_KEY  As Parent,
    Null() /* HIER_2_DESC */ As Parent_Desc,
   "LEVEL 3"   As Member_Level,
   "LEVEL 2"   As Parent_Level,
    '1'         As ApplyOrder
FROM [lib://Hierarchy/Hierarchy-Sample.xlsx]
(ooxml, embedded labels, table is Sheet1);

NoConcatenate 
H_Data:
Hierarchy(Member, Parent, NodeN_deame, 'My_Parent','My_Node', 'My_Path', '\', 'My_Depth')
Load * 
Resident Source_Data;

Drop Table Source_Data;

Hope this helps,

Best regards,