Data Update logic with unique key

Question

I am trying to find out a solution for below case - I tried regular insert update logic and that does not solve.

Scenario

There will be data updates coming in and a table need to look at these updates and then decide one of the following. Table has customer and State along with its active status, State Rep and an alpha numeric unique key in it.

Incoming Data Scenarios:

* If a record (Customer & State combination) exist in the table with Active Status (1) then don’t do anything
* If a record (Customer, State) is coming in but it exist with inactive status (0) within the table, then insert the record and make it Active (1)
* If a Customer comes in with a different State then update the State and make the Rep column to Null
* If it’s a brand new customer , then insert the record and generate a new key for that row (with Active status=1)

Here is the desired result (Final format of the table after making the updates/inserts)

Yoshiro_Fujimori · Answer

Hi @jay_viz ,

I used Find Replace tool to keep the original records and just add fields from the other table.

Join tool did not work for this usage, as it generated multiple rows and complicated the following processes.

(But Find Replace tool only accepts String fields for matching keys. So I changed the data type beforehand.)

jay_viz · Answer

@Yoshiro_Fujimori Thank you, I think you got it what I was trying to do. Apologies if the instructions were unclear.

Do you mind explaining what the "Find and replace" does in those 2 cases. For the keys, as long as its a unique number, so what you did should be fine.

Yoshiro_Fujimori · Answer

Hi @jay_viz ,

As your conditions to update the table were not straightforward, it was hard for me to follow.

I tried to follow your condition as below.

Workflow

As there was no description how to set a key, I generate random strings with MD5 formula.

Output

I hope this may be of some help.

UpdateUniqueKey.yxmd