Need help with coalesce (trying to prevent duplicates)

[robhaas]

I am importing data with a field that has information such as the following which is going into the source field 'u_entitlement_path'.

SYSTEM/PROD/GROUP\GROUPNAME

I am parsing the SYSTEM and GROUPNAME from the source field using RegEx and putting each of them into their own fields on the target table. I am needing to prevent duplicate entries from being added to the target table based on both the system and the groupname.

I have tried turning coalesce on for both field maps, but the new entries still get added. I am not too familiar with coalescing either so I'm sure lack of knowledge is definitely key. All help is appreciated.

[Chuck Tomasi]

Give it a try and let me know how it goes. The solution may be simpler than we think.

[robhaas]

Well that actually won't work. Been a long day and my brain just isn't working. I cannot coalesce on those fields. Reason being is like I tried to explain in the first post. I am parsing those. I do that with the following script on the table transform map:

var arr = source.u_element_path.split(/\/|\\/);

  var system = arr[0], groupname= arr[4];  

  target.u_system = system;

  target.u_groupname = groupname;

I know that is redundant a bit and I am going to correct it.

So the source field is simply u_entitlement_path which contains the string SYSTEM/PROD/GROUP\GROUPNAME. The system and groupname are parsed out with the above.

Maybe in this logic, I would only need a BR as Abhinay stated below? I am going to look at that option as well.

[Chuck Tomasi]

Ah, thanks for the reminder. I forgot you were parsing those out. I believe you can do an onBefore transform script and it will populate those fields so you can use a standard coalesce. It's been a while since I did this, but I do recall a similar situation when I was a customer and had to manipulate the employee ID string before saving it and coalescing.

[Abhinay Erra]

I dont think we will be able to coalesce on the parsed field values.   But I may be wrong, I am curious to know how we could do?

[srinivasthelu]

Hi Robert,

by reading through the post, it seems you are almost there.I had this scenario before and below is what I did.

1. Create 2 field maps one for each field with   "Use source script" checked

2.   And map each field separately with coalesce on.  

Below is the further explanation:

1. First Field map will have target field has system and below script

answer = (function transformEntry(source) {

var arr = source.u_element_path.split(/\/|\\/);

var system = arr[0], groupname= arr[4];

return system;

})(source);

Second one would be

answer = (function transformEntry(source) {

var arr = source.u_element_path.split(/\/|\\/);

var system = arr[0], groupname= arr[4];

return groupname;

})(source);