Creating a Hierarchy Tranformation

CloverDX Platform
1

I need to create a hierarchy from a flat, 2 column child-parent table. the data looks like this:


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!

2

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,