How to merge two lists or dedup a list string in a reformat

CloverDX Platform
1

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.

2

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.

3

Thanks Alex. That worked perfectly.