I am new to CloverETL, QuickStart was awesome, but need more help. I need to map records in various fields based on their values and label the null or unknown values as "Unknown".
For example: A client file with Race information. Change caucasian to WHITE, change African American to BLACK, everything else to UNKNOWNn (including null)
I know I can use a LookUps to do part of this, but as it seems, LookUps are only 1-to-1. I need to be able to replace all records that fall outside of the 1-to-1 and also null records.
For example: A client file with Race information. Change caucasian to WHITE, change African American to BLACK, everything else to UNKNOWNn (including null)
I know I can use a LookUps to do part of this, but as it seems, LookUps are only 1-to-1. I need to be able to replace all records that fall outside of the 1-to-1 and also null records.
-
Hi,
You can use LookupJoin in which you will need to set "Left outer join" attribute to true. As you can see in transform editor (under transformation attribute), the component has two inputs - $in.0 represents input records from the connected edge; the other one ($in.1) is used for joined records from lookup table. When left outer join is turned on, the component will return also those records that have not been found in lookup table. To map a particular value to fields that has not been found in lookup, you will only need to check whether the value from input port $in.1 is null (in transform editor – available under transformation attribute). See the sample CTL code below (adjust it accordingly to your use case).function integer transform() {
if(isnull($in.1.key)) {
$out.0.outField = "UNKNOWN";
} else {
$out.0.outField = $in.1.value;
}
return OK;
} -
Thank you slechtaj, your suggestion absolutely helped. There is however 1 more case that you did not address. The case where the lookUp table does not match anything and the field is not null.
Using the same race example as before. if x = black, y = white and null = unknown, what if the value is z or something other than what I have identified in the lookup.
I am trying to match all cases. If data comes in and it has not been identified in the lookup I would like to be able to mark it as "Unknown".
Also, is there a fast way for me to use the same lookup on multiple fields without using a new component. -
Hi, Zram_K,
You can just add a new condition into if, searching for keys non-existing in your lookup.if(isnull($in.1.key) || lookup(my_lookup).count($in.1.key) == 0) {
// Do something.
}
And regarding your next question about multiple fields, I am not exactly sure what you mean. Can you please rephrase and describe that? If your lookup contains everything needed for another field, you can use it in Transform editor either in Transform GUI or in source CTL (see http://doc.cloveretl.com/documentation/ ... -ctl2.html)
Best regards, -
Now that I have had more time test, it does not seem like the first answer nor the most recent is doing what I am asking. So let me start by reiterate what I am trying to do:
My Ethnicity lookup renames fields based on a key.
1. When incoming data fields are null = "Unknown".
2. When incoming data fields are not-null, but not in the lookup = "Other".
Here is the code I've created based on CloverCare Support:
if(isnull($in.1.Old_Ethnicity) || lookup(Ethnicity).count($in.1.Old_Ethnicity) == 0){
$out.0.Ethnicity = "Unknown";
} else {
$out.0.Ethnicity = $in.1.New_Ethnicity;
}
Seems like all exceptions = "Unknown" regardless of null or missing key.
Thank you in advance for the help. -
Hi,
Well, in previous posts you never mentioned "Other" as possible output. Considering that, your code should probably look like:
//ethnicity not set
if (isnull($in.0.Ethnicity)) {
$out.0.Ethnicity = "Unknown";
//ethnicity not presented in lookup
} else if ( lookup(Ethnicity).count($in.0.Ethnicity)<=0) {
$out.0.Ethnicity = "Other";
//ethnicity set and presented in lookup
} else {
//take value from lookup
$out.0.Ethnicity = $in.1.New_Ethnicity;
}
Please sign in to leave a comment.
Comments 5