topic Re: Hierarchy - how to concatenate tables with different depth nodes in QlikView

Hierarchy - how to concatenate tables with different depth nodes

Tue, 20 Mar 2012 13:47:33 GMT

Hi All,

using below code I mean to get chart of accounts from several tables. All tables have identical structure, only node depth changes - all tables node depth is 3, one's node depth is 5. After reload, instead of desired one table Accounts I arrive with three tables: Accounts, Accounts-Expanded Nodes and $Syn. What could be done to get one table one?

DBS:
SELECT DB_Name FROM Configuration;
Accounts:
LET NoOfDBS = NoOfRows('DBS');
FOR i=0 TO $(NoOfDBS)-1
LET DB_Name = peek('DB_Name',$(i),'DBS');
HIERARCHY(Acc_AccId, Acc_ParId, Acc_Name)
SELECT 
          '$(DB_Name)' AS DB_Name, 
          Acc_Type, 
          Acc_Name,
          Acc_AccId, 
          Acc_ParId
FROM CDN_$(DB_Name).CDN.Accounts;
NEXT

Regards,

Przemek

Re: Hierarchy - how to concatenate tables with different depth nodes

kuba_michalik — Mon, 26 Mar 2012 09:09:49 GMT

I am not sure why it ends like that - Accounts-Expanded Nodes is an automatic table created during hierarchy load and shoud get auto-dropped, but probably something goes wrong because of the loop. I would first load the whole adjacent node table (via the loop) and then reload it using hierarchy load:

DBS:
SELECT DB_Name FROM Configuration;
AccountsAdjacent:
LET NoOfDBS = NoOfRows('DBS');
FOR i=0 TO $(NoOfDBS)-1
LET DB_Name = peek('DB_Name',$(i),'DBS');
SELECT
          '$(DB_Name)' AS DB_Name,
          Acc_Type,
          Acc_Name,
          Acc_AccId,
          Acc_ParId
FROM CDN_$(DB_Name).CDN.Accounts;
NEXT

Accounts:
HIERARCHY(Acc_AccId, Acc_ParId, Acc_Name)
LOAD * 
RESIDENT AccountsAdjacent;

DROP TABLE AccountsAdjacent;

Hierarchy - how to concatenate tables with different depth nodes

hic — Mon, 26 Mar 2012 09:30:33 GMT

The Hierarchy prefix makes a transformation that needs the entire Adjacent Nodes table - an output record from this transformation has information from many of the input records. So, concatenation of two different Hierarchy prefix statements will lead to unpredictable results.

So, Kuba's solution is completely right. First, create the entire Adjacent Nodes table, then do the Hierarchy transformation.

/HIC

Re: Hierarchy - how to concatenate tables with different depth nodes

Mon, 26 Mar 2012 16:21:33 GMT

Thank you, Kuba, thank you Henric. It did the job. One thing for future readers, though - when doing hierarchy transformation join account and parent id with db, otherwise hierarchy will go wild.