Populate parent IDs

Support/help with CloverETL (4.9) and CloverDX (5.0 or newer) implementation problems

Posts: 3
Joined: Tue Mar 12, 2019 4:11 am

Populate parent IDs

Postby evangeloslef » Wed Mar 20, 2019 9:37 pm


I created a project using Clover and I got stuck into a specific point because I don't know how CloverDX can handle such a scenario.

I am creating IDs from input spreadsheet and I want to create a tree by populating parent IDs.
In my input I have also Category Type that consist from Category/Topic/Variant/Procedure

The way I want to build the tree is that:
- all the Procedure IDs will have as a parent ID the Variant IDs
- all the Variant IDs will have as a parent ID the Topic IDs
- all the Topic IDs will have as a parent ID the Category IDs
- and the Category that will always be the top I will not populate anything in the Parent ID

To give a better understanding of what I am trying to do I attached a photo that shows my input into my expected output.

Populate Parent Ids.JPG
Example of the input and expected output.
Populate Parent Ids.JPG (109.1 KiB) Viewed 874 times

Posts: 136
Joined: Wed May 03, 2017 12:10 pm

Re: Populate parent IDs

Postby bartonv » Fri Mar 22, 2019 12:56 pm

Hi Evangeloslef,
it appears that your goal can be achieved simply by using the Reformat component. I have attached an example solution to your challenge in the form of a graph. Worth noting is the following:
  • I assume that "Category" is always on top and "Topic" never forgoes its respective "Category". "Variant" never foregoes its respective "Topic" and "Procedure" never foregoes its respective "Variant".
  • Note the usage of 3 local variables in the Reformat component transformation mapping. The idea is that CloverDX is checking the type of record using the IF condition and whenever it finds any of the parent ones, it will assign their ID to the respective local variable.
  • Then, CloverDX would simply pull the parent_ID from the proper variable when needed.
If my assumption is not correct please elaborate on the conditions that need to be met.
Kind regards,
(2.94 KiB) Downloaded 49 times
Vladimir Barton
CloverCARE Support

Visit us online at http://www.cloverdx.com

Posts: 24
Joined: Wed Oct 24, 2018 2:38 pm

Re: Populate parent IDs

Postby andras.csore » Fri Mar 22, 2019 2:15 pm

Hello Gents,

I also add a little different approach => what if the 'levels' is unknown amount?
First i create one solution what is practically is the same as Vladi solution (what is the most cleanest and fast solution).
And below that i add another solution, what handle in theory the case of unknown (unlimited) amount of CategoryTypes (parent/child relation).

First i wondering to use some ARRAY way of solving, and also think about the Rollup.
But for curiosity i choose a 'looks difficult' approach: more component and less code based, and use DeDup components mainly.
I like to highlight: my solution is not so effective (especially performance wise, and so on), and may possible to do in less step, but at general it may show some DeDup usage for logical cases.
Main logic steps:
  1. First i get the 'level' names to 'level' numbers, and join back.
  2. Calculate for each row some 'group_id' (x_groupid) (to keep the same level CategoryTypes under one 'groupid', its later helps to find out the last row in the same group (what may the parent of some child)
  3. DeDup the rows, to get only the 'last' rows in group, and use it later as possible 'parent' value
  4. Join back to 'original' rows the possible 'parent rows' cases (connect by logical 'level') => in here we get ' lot of not good result' rows also, what we handle in next steps.
  5. We filter out rows, where we know the joined parent cant be the correct one
  6. DeDup the remained rows, to get back the 'nearest' parent
  7. And we done.
Please see the attached Graph+Sample Excel (graph and code inside is commented in some level).
(11.05 KiB) Downloaded 57 times

Posts: 397
Joined: Wed Aug 15, 2012 8:18 am

Re: Populate parent IDs

Postby imriskal » Fri Mar 22, 2019 2:43 pm

Nice, thanks for an alternative approach. Good job, Andras.
Lubos Imriska
CloverCARE Support

Visit us online at http://www.cloverdx.com