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!
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!
-
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? -
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.
Please sign in to leave a comment.
Comments 2