Update / Insert logic

[AlterIT]

Background: Base table has customer records for each state and all of them have a rank within the state. I am trying to insert new customer records to each state with a rank higher than maximum rank within each state or move customer from one state to another with the same rank logic.

As you could see, when customer 101 moves from OH to CA it got rank 1 since CA does not have any records, at the same time when another customer (201) got added to CA it got rank 2 since rank 1 is already occupied by customer 101. 

Similarly for customer 112, it got added to OH and since its max rank is 10, this new customer will get a rank of 11 and so on.

What I tried: I have designated customer ID as primary key in the base table and tried to load the Update/Insert records using insert/update logic. However, I am unable to find a logic to get the Rank working. I have attached the data file I am using as well. Thank you

[AlterIT]

Included the data layout as an image here

[BS_THE_ANALYST]

@AlterIT can you try this workflow:

[AlterIT]

@BS_THE_ANALYST this looks great. 

One question I see you have used the base table on the left side.. In the final solution should I use the table on the left side and any insert/updates on the right side. Trying to make sure it was not a one time thing to load the data rather its part of the solution each time it compare current table and any update/inserts

[AlterIT]

@BS_THE_ANALYST One thing I noticed, if the CA already  has a Customer record with Rank=1 even the customer moved from OH to CA with a Rank gets Rank =1 which is causing issues. We ideally want 3 distinct ranks for customers in CA. I tried modifying the "New Rank" field expression and could not find much luck.

Or even a dynamic counter corresponding to each customer within a state is also fine.

[BS_THE_ANALYST]

@AlterIT Honestly, this one has fried my brain. Nevertheless, I think building a macro would be a nice idea here aswell. I ran a few tests below aswell to check it was working as intended.

1. Running through with your initial state 'base table' and connecting it to the first Customers/Customer Transfers table.

2. Now, I took the output of this table. Reconnected it to the workflow, and added a New (theoretical) Customers/Customer Transfers table. It seems to be functioning as expected. 
This was lookup table:

3. Results:

I edited the logic slightly on the Multi-Row. Can you run some tests to see if it works for you. 

[AlterIT]

@BS_THE_ANALYST Thank you so much for investing so much time on this. I was thinking to simplify this and is there a way we can just get an incremental counter for each customer within each state instead of complex ranking. At the end, after the insert/ update process it just need a counter which counts each customer within the state.

[TheOC · Accepted solution]

Hi @AlterIT 
I have had a go at your issue, and produced the following workflow:

This workflow calculates a maximum rank (using summarize) from the base table. This is used to create new ranks. If the state does not have a max rank, this is set to 0:

Each new record is given a record id (grouped by state, using a tile tool):
 

This tile number is added to the maximum rank, giving us our new rank numbers:

Then, we have the new records sorted, but need to exclude any that have been in states previously. I used a join in a slightly crafty way to remove any records from the base table if they join to the new records on customer ID:

Finally, I unioned the three results from my join

(Left: Base Table Records unjoined

Join: Customer IDs removed due to conflict

Right: Insert Records unjoined).

I also sorted by customerID just to make it easier to validate:

Please find the workflow attached as YXZP for your convenience. I've tried to annotate with containers and our Bulien template where possible, but let me know if you have any questions.

Kind Regards,

Owen

[AlterIT]

@TheOC  This is incredible solution, Thank you for your investing time to help with this solution. 

One follow up question, I see you added a hardcoded formula "Max_Rank=0" to "Join the max rank to each new record, based on state" component, I am curious what was the purpose of that specific component. Thank you for your help once again.

[TheOC]

hey @AlterIT 
No problem at all!

That part is actually to force a max rank of 0 if a max rank does not exist. To put that into an example, if we had a state that had a rank of 100, we would want new ranks to start at 100. For states that do not yet have a rank, the new ranks should start at 0. 

This setting of max rank to 0 only occurs to the records found in the right outer join, in other words, the states that exist in the 'insert records' table, but not in the main table.

I hope this makes sense, let me know if not!

Kind Regards,

Owen