Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
samu
Partner - Contributor
Partner - Contributor

Automatic creation of chart of accounts hierarchy (NodeID/ParentID)

Hi,

I'm currently trying to automatically create hierarchy for our chart of accounts hierarchy.

Basically our account structure in the database looks like as table below. I have made field NodeID as running number in the script but now I would have to create ParentID field within script by using Indentaion field. Any ideas? As far as I can remember the result should be something like as shown on the ParentID field I created manually.

Thanks in advance

Samu

Account noAccount nameIndentation
NodeIDParentID (example)
900XYZ01
910XYZ121
920XYZ232
1030XYZ343
1035XYZ353
1040XYZ363
1041XYZ373
1045XYZ383
1050XYZ393
1055XYZ3103
1080XYZ3113
1091XYZ2132
1095XYZ31413
1100XYZ41514
1101XYZ41614
1115XYZ41714
1116XYZ41814
1119XYZ32013
1120XYZ42120
1121XYZ42220
1128XYZ42320
1129XYZ42420
1150XYZ42520
1158XYZ42620
1190XYZ42720
1198XYZ42820
1200XYZ33013
1201XYZ43130
1209XYZ43230
1220XYZ43330
1221XYZ43430
1222XYZ43530
1228XYZ43630
1230XYZ43730
1238XYZ43830
1250XYZ43930
1258XYZ44030
1260XYZ44130
1268XYZ442

30

2 Replies
pover
Luminary Alumni
Luminary Alumni

If the accounts are properly ordered, you should be able to do it if you use the previous() or peek() in the Load and save and always save the last value of each indentation in each row. Here's an example below (not tested).

Load Account no,
Account name,
Indentation,
NodeID,
if(Indentation=0,NodeID,previous(Last_Indentation_0)) as Last_Indentation_0,
if(Indentation=1,NodeID,previous(Last_Indentation_1)) as Last_Indentation_1,
if(Indentation=2,NodeID,previous(Last_Indentation_2)) as Last_Indentation_2,
if(Indentation=3,NodeID,previous(Last_Indentation_3)) as Last_Indentation_3,
if(Indentation=0,'',
if(Indentation=1, Last_Indentation_0,
if(Indentation=2, Last_Indentation_1,
if(Indentation=3, Last_Indentation_2,
if(Indentation=4, Last_Indentation_3))))) as ParentID
From table.qvd (qvd);

If the previous() doesn't work use peek('Last_Indentation_3') which uses single quotes around the field name.

Regards.

marcohadiyanto
Partner - Specialist
Partner - Specialist

hi, you can try hierarchy.

Example:

Hierarchy(NodeID, ParentID, NodeName) Load

NodeID,

ParentID,

NodeName,

Attribute

From data.xls (biff, embedded labels, table is [Sheet1$];

i got this from qlikview help.