Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 no | Account name | Indentation | NodeID | ParentID (example) |
900 | XYZ | 0 | 1 | |
910 | XYZ | 1 | 2 | 1 |
920 | XYZ | 2 | 3 | 2 |
1030 | XYZ | 3 | 4 | 3 |
1035 | XYZ | 3 | 5 | 3 |
1040 | XYZ | 3 | 6 | 3 |
1041 | XYZ | 3 | 7 | 3 |
1045 | XYZ | 3 | 8 | 3 |
1050 | XYZ | 3 | 9 | 3 |
1055 | XYZ | 3 | 10 | 3 |
1080 | XYZ | 3 | 11 | 3 |
1091 | XYZ | 2 | 13 | 2 |
1095 | XYZ | 3 | 14 | 13 |
1100 | XYZ | 4 | 15 | 14 |
1101 | XYZ | 4 | 16 | 14 |
1115 | XYZ | 4 | 17 | 14 |
1116 | XYZ | 4 | 18 | 14 |
1119 | XYZ | 3 | 20 | 13 |
1120 | XYZ | 4 | 21 | 20 |
1121 | XYZ | 4 | 22 | 20 |
1128 | XYZ | 4 | 23 | 20 |
1129 | XYZ | 4 | 24 | 20 |
1150 | XYZ | 4 | 25 | 20 |
1158 | XYZ | 4 | 26 | 20 |
1190 | XYZ | 4 | 27 | 20 |
1198 | XYZ | 4 | 28 | 20 |
1200 | XYZ | 3 | 30 | 13 |
1201 | XYZ | 4 | 31 | 30 |
1209 | XYZ | 4 | 32 | 30 |
1220 | XYZ | 4 | 33 | 30 |
1221 | XYZ | 4 | 34 | 30 |
1222 | XYZ | 4 | 35 | 30 |
1228 | XYZ | 4 | 36 | 30 |
1230 | XYZ | 4 | 37 | 30 |
1238 | XYZ | 4 | 38 | 30 |
1250 | XYZ | 4 | 39 | 30 |
1258 | XYZ | 4 | 40 | 30 |
1260 | XYZ | 4 | 41 | 30 |
1268 | XYZ | 4 | 42 | 30 |
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.
hi, you can try hierarchy.
Hierarchy(NodeID, ParentID, NodeName) Load
NodeID,
ParentID,
NodeName,
Attribute
From data.xls (biff, embedded labels, table is [Sheet1$];
i got this from qlikview help.