What is COALESCE explain with example

[Apsara P]

Please Help me

[rishabh33]

Hi @Dan O Connor ,

what if we don't want to add fourth row(empid :004) which is not present in sn ?

[Dan O Connor]

Rows are records. This is data once loaded will appear as a record in a ServiceNow table.

If for example you had a data load with 10 records but only wanted to use lets say 7, you would just remove the three rows you don't want from the data file, so it doesn't import in the first place. 

[AshleyAu3]

Thanks for the explanations. 

What should be done if the ‘Employee ID’ in the source data is represented as an integer?

[KV_CTS]

Nice example @Dan - would also be worth mentioning that there is also an option to use multi-field coalesce meaning ALL coalesced fields must match for an update to occur. If any field differs, ServiceNow creates a new record.
So,

Target Record	Incoming Record	Match Result	Action
John Smith	John Smith	Both match	UPDATE
John Smith	John Doe	First matches, Last differs	INSERT (New record created)

and then we also have the dynamic / more advanced Conditional Coalescing. Think of it like "Search ONLY IF" especially useful when at times, incoming data has missing or bad information like "UNKNOWN", or is left blank.

Example: We have 100 People with ID = "UNKNOWN"

WITHOUT Conditional Coalesce (Standard Coalesce)

1. Person 1 ("Alice", ID: "UNKNOWN") arrives:

   • System searches for "UNKNOWN". Not found.

   • System creates Record #1: Name = Alice, ID = UNKNOWN

2. Person 2 ("Bob", ID: "UNKNOWN") arrives:

   • System searches for "UNKNOWN". MATCH FOUND! (It finds Alice).

   • System overwrites Record #1: Name = Bob, ID = UNKNOWN. (Alice is deleted/erased!)

3. Person 3 - and so until 100 arrive:

   • Each person matches Record #1 and overwrites the previous name.

Final Result in Database? Only 1 total record (Person #100). The other 99 people were overwritten and lost forever.

Now, WITH Conditional Coalesce

1. Person 1 ("Alice", ID: "UNKNOWN") arrives:

   • System sees "UNKNOWN" Rule triggers: "DO NOT SEARCH".

   • System creates Record #1: Name = Alice, ID = UNKNOWN.

2. Person 2 ("Bob", ID: "UNKNOWN") arrives:

   • System sees "UNKNOWN" Rule triggers: "DO NOT SEARCH".

   • System creates Record #2: Name = Bob, ID = UNKNOWN.

3. Person 3 - and so on until 100 arrive:

   • System bypasses the search for every row and creates Records #3 through #100.

Final Result in Database? 100 distinct records (Alice, Bob, Charlie, etc.), all safely stored in your system.

Please like and "Mark Helpful" if you liked my example & explanation. For you, it takes a second, for me, it does a great deal. Thank you! 🤗

[KenO36]

I'm studying for my CSA and this was a simple explanation of coalesce and its use case. Thanks!