I have two string fields that each has a list of states separated with semi-colons that I would like to combine into one field but without duplicating any of the states. For instance, the data coming into the reformat would looke like this:
RecordID LicensedStates LocationStates
100 PA;AZ;CA PA;AZ;AL
101 PA PA
102 AZ;CA CT
The result I would like to get out of the reformat would be:
RecordID CombinedStates
100 PA;AZ;CA;AL
101 PA
102 AZ;CA;CT
I've tried to find ways of doing this but after looking through the documentation for Normalizer, Denormalizer, and a few Java lines of code and just have no idea what i'm doing.
Any advice you have would be appreciated.
-
Hi Michael,
Just use Map (former Reformat) component. You can apply transformation similar to this one:
//#CTL2
string []lLicensedStates ;
string []lLocationStates;
// Transforms input record into output record.
function integer transform() {
//create lists of states from delimited input data
lLicensedStates = $in.0.LicensedStates.split(";");
lLocationStates = $in.0.LocationStates.split(";");
//iterate through LocationStates and check for each state presence in LicensedStates list
foreach (string state : lLocationStates){
if (! state.in(lLicensedStates)){
lLicensedStates.append(state);
}
}
//convert list ot states into string, use ";" as delimiter
string CombinedStates = join(";",lLicensedStates);
$out.0.RecordID=$in.0.RecordID;
$out.0.CombinedStates=CombinedStates;
return ALL;
}It may not be the best performing way, but if your lists of states consit of only handful items, it will perform OK.
Please sign in to leave a comment.
Comments 2