Compare ID from two datasets and create a new column

[ssrip4]

Hello community,

I am trying to map data from two places, one is the data from a CSV file and other is data retrieved from a database.

CSV Data

ID	Name	Action
101-ABC-6ZX	Alpha	A
102-ABC-6XZ	Bravo	A
103-ABC-6CV	Charlie	R
104-ABC-6BN	Delta	D

Database Data

ID	Code
101-ABC-6ZX	ESC1
103-ABC-6CV	ESC2
104-ABC-6BN	ESC3
105-ABC-6NM	ESC4

Result Data in CSV

ID	Name	Action	Change
101-ABC-6ZX	Alpha	A	Revise
102-ABC-6XZ	Bravo	A	Add
103-ABC-6CV	Charlie	R	Revise
104-ABC-6BN	Delta	D	Delete

Now, I am trying to compare the ID data from CSV with the data from DB across all the rows to find a match.

Then, I would like to add a new column called Change based on a formula.

1) If ID from CSV matched the ID from DB and the "Action" column is "Not Equal to D" then the change should be "Revised". If it equals to "D" then it should be "Delete".

2) If the ID from CSV is not found in the ID from the DB and the "Action" column is "Equal to A" then the change should be "Add".

Can anyone help me with this?

Thank you

[Luke_C · Accepted solution]

Hi @ssrip4 

Here's how I'd approach this:

1. Join the data together on ID
2. Union the fallouts from the csv (this gives the effect of a left outer join)
3. Use an IF statement to assign your change logic

[ssrip4]

Hello @Luke_C, thank you for the help. It works perfectly