Need help with coalesce (trying to prevent duplicates)

robhaas · ‎01-09-2017

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 · ‎01-09-2017

Hi Robert,

The purpose of coalescing is to determine uniqueness - when is it time to create a new record and when is it time to update an existing one?

By strange coincidence, I just learned a few minutes ago that you can script coalesce functionality. This may help.

Example conditional coalesce scripts

robhaas · ‎01-09-2017

Hi Chuck,

Just realized I didn't fully explain myself. I have 3 fields which are SYSTEM, GROUPNAME, and APPROVER. Basically I am wanting to look at the target table and compare the parsed values of system and groupname from the source table, and the approver. If the system, groupname, and approver all match target data, ignore. If the approver is different, update the approver field only.

The system and groupname should not change.I just found that article as well. I am trying to make more sense of it. I have a feeling I am making this a lot more difficult than it has to be as that is my nature. Does this make sense?

Chuck Tomasi · ‎01-09-2017

Hi Robert,

Thanks for the information. Any time you go beyond the simple coalesce feature of "does this field, or fields match" as a unique key to determine if it's an insert/update operation, you're looking at some scripting.

I've read your above statement several times and trying to keep the simplest approach.

If you coalesce on the System and Groupname fields only, it will create a new record if it does not find an exact match for that. If it does, then it updates any existing records. If the value of approver has not changed, there's no update. If it has, it will update that.

Does that sound like what you want?

robhaas · ‎01-09-2017

This does sound correct as I run through it in my head.