I need to create a hierarchy from a flat, 2 column child-parent table. the data looks like this:
Ideally I'd like the data to look like this after the transformation:
So, in essence, for each lowest level child ID, generate a full ID hierarchy.
I'm a little stumped as to how to accomplish this in CloverETL....any help will be greatly appreciated!
CHILD_ID PARENT_ID
1100 1000
1200 1100
1300 1200
1400 1100
1500 1000
1600 1400
1700 1200
1800 1600
1900 1000
2000 1800
...
Ideally I'd like the data to look like this after the transformation:
1000|1100
1000|1100|1200
1000|1100|1200|1300
1000|1100|1400
1000|1500
1000|1100|1400|1600
1000|1100|1200|1700
1000|1100|1400|1600|1800
1000|1900
1000|1100|1400|1600|1800
So, in essence, for each lowest level child ID, generate a full ID hierarchy.
I'm a little stumped as to how to accomplish this in CloverETL....any help will be greatly appreciated!
-
Hi, skylaneffz,
if your input file is the same as you posted, i.e. always sorted by CHILD_ID and the numbers from the root to leaves are always sorted in ascending order (e.g. 2000 can not be a parent of 1500), you can use a Reformat component with the following CTL code.//#CTL2
map[string, string] childParent;
function integer transform() {
string res = $in.0.CHILD_ID;
childParent[res] = $in.0.PARENT_ID;
string act = $in.0.CHILD_ID;
while (childParent.containsKey(act)) {
act = childParent[act];
res = act+"|"+res;
}
$out.0.path = res;
return OK;
}
If your input file was somehow different, you should save the input records into the lookup table first and only then you can use similar approach.
Best regards,
Please sign in to leave a comment.
Comments 1