Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

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
Highlighted
Luminary
Luminary

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

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.

Highlighted
Partner
Partner

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

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.