Hello community,
I am currently trying to do a comparison of data between two tables. As of now, I am joining them both on a Unique ID and then comparing data from two sources.
One is from a CSV file and another one is from a database. Below is the sample data from both sources.
Table from CSV
| Book ID | Book Name | Book Genre | Book Cost |
|---------|------------------------------|------------------|-----------|
| B1G3F | The Great Gatsby | Classic Fiction | $10.99 |
| H2B8X | The Hobbit | Fantasy | $15.99 |
| M9C4D | Becoming | Autobiography | $18.99 |
| S7P6A | The Silent Patient | Thriller | $12.99 |
| E5T1R | Educated: A Memoir | Memoir | $14.99 |
Table from DB
| Book ID | Book Name | Book Genre | Book Cost |
|---------|------------------------------|------------------|-----------|
| B1G3F | The Great Gatsby | Modern Classics | $11.49 |
| H2B8X | The Hobbit | High Fantasy | $16.49 |
| M9C4D | Becoming | Inspirational | $19.49 |
| S7P6A | The Silent Patient | Psychological | $13.49 |
| Z4K2U | Where the Crawdads Sing | Mystery | $17.99 |
| F5L1Q | Normal People | Contemporary | $16.99 |
| R3T8V | The Alchemist | Philosophical | $12.99 |
| J6W3E | Atomic Habits | Self-help | $21.99 |
| D2S7B | To Kill a Mockingbird | Historical | $14.99 |
| P4X9G | The Four Agreements | Spirituality | $13.99 |
Now, the objective for me is to Join both of these records and Union all records from Left, Join, and Right from the Join tool. Then I need to use a Formula tool to create a new column called Action.
The value of this action is based on the data.
Add New Book to Record - This should happen when ID from Table 1 is not present in Table 2. Then Add Record
Update Values - This should happen when ID from Table 1 is present in Table 2. Then compare Book Name, Book Genre, Book Cost and if there is any difference even in one column, then value of Action should be Update Book Data.
Attached is the sample file.
Expected Output
Book ID Book Name Book Genre Book Cost Action
| B1G3F | The Great Gatsby | Modern Classics | $11.49 | Revise Data |
| H2B8X | The Hobbit | High Fantasy | $16.49 | Revise Data |
| M9C4D | Becoming | Inspirational | $19.49 | Revise Data |
| S7P6A | The Silent Patient | Psychological | $13.49 | Revise Data |
| E5T1R | Educated: A Memoir | Memoir | $14.99 | Create Record |
| Z4K2U | Where the Crawdads Sing | Mystery | $17.99 | Create Record |
| F5L1Q | Normal People | Contemporary | $16.99 | Create Record |
| R3T8V | The Alchemist | Philosophical | $12.99 | Create Record |
| J6W3E | Atomic Habits | Self-help | $21.99 | Create Record |
| D2S7B | To Kill a Mockingbird | Historical | $14.99 | Create Record |
| P4X9G | The Four Agreements | Spirituality | $13.99 | Create Record |
