Replace entire rows based on some common source

[Neeta_Latwal]

Hi All,

I have two report, I want to replace data in sheet1 where the source is GLO based on concatiation of entity and currecy with the data available in sheet2.

Sum of sheet1 is matching with sheet2 wherever i want to replace the data.

Sheet1

entity	currency	date	amount	description	Source	concatination(entity, currency)
124	EURO	05/06/2023	100	abc	SSU	124EURO
167	USD	05/26/2023	200	DEF	GLO	167USD
189	INR	06/25/2023	200	123	GLO	189INR

Sheet2

entity	currency	date	amount	Source	concatination(entity, currency)
167	USD	05/30/2023	50	GLO	167USD
167	USD	05/26/2023	150	GLO	167USD
189	INR	06/25/2023	20	GLO	189INR
189	INR	06/27/2023	180	GLO	189INR

Thansk,

[lekshmirl]

Please use the attached WF for getting the results.

[p-g]

1. You can apply Filter tool to Sheet 1 Table and filter out all the rows with Source = "GLO". 

2. Use "Join" tool on the resultant "Yes" output and Sheet2 Table based on concatenation column

3. Configure the Join (J) output to use the columns from Sheet2 and rename them accordingly.

4. Union Join output (J), Left (L) output and the "No" output from step 1 Filter

If you need the final output in the exact same order as input for Sheet1, use RowID tool at the beginning and sort by RowID at the end. With this method, you will have 5 rows in output as 2 rows each of 167USD and 189INR will replace 1 row each of the same in Table 1.

.

[Neeta_Latwal]

can you share the screenshot of worklow, that will be great

[p-g]

Here you go! Ensure you rename the columns with Right_ with the appropriate column names and uncheck the equivalent Left Columns as necessary in the Join Step.