Denormalizer - adding additional data to output field

CloverDX Platform
1

Good morning!

I’m creating a graph using denormalize. Issue: I have a two column spreadsheet. the first column is an ID field and the other a ROLE field. The data in the spreadsheet has the same ID [key field] and different roles for each row of data. When I run the process, I only get the last role for the demormalized data. What I want to do is add the roles to the one ROLE column so I get 1 ID with all the roles for that ID; separated by commas. Do I need to do CTL coding? If so, which direction should I go in.

Thank you!

2

Hello,
although the denormalizing transformation doesn’t have to be written in CTL ( both the definitions in transformation tab and Java are possible too), I would recommend to use this one.

The CTL template is described in the user’s manual
http://www.cloveretl.com/documentation/ … lizer.html

See the graphRollupTL_AsDenormalizer.grf in the SimpleExamples project that illustrates the use of Denormalizer and the CTL transformation. You can simply copy the source code and modify it to fit your requirements:
- rename the variables
- delete the numRec variable
- change the separater

Do you still have any difficulties?

3

Your denormalizer should have key set to ID and the CTL code should look like this:


//#TL
string tmp_role;   // global var which holds all the roles for one ID

append(){
  if (tmp_role == "")
       tmp_role=$ROLE;
  else
      tmp_role=concat(tmp_role,',', $ROLE);   // just concat roles into one large string
}

transform(){
  $ID:=ID:
  $ROLE:=tmp_role;

}

clean(){
 tmp_role="";  // re-set the tmp_role to empty string for next ID group
}

As a result, for each ID group, you get 1 record on output with ID and ROLE as a comma separated list of roles.