Join the Alteryx Community’s Maveryx Summer Cup event! Compete, network with others, and earn your gold through a series of challenges from July 24th to August 11th. Learn more about the event here.

Bengaluru, IN

Welcome to the Bengaluru User Group

Click in the JOIN GROUP button in Home to follow our news and attend our events!

Replace entire rows based on some common source

Neeta_Latwal
7 - Meteor

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

 

entitycurrencydateamount descriptionSourceconcatination(entity, currency)
124EURO05/06/2023100abcSSU124EURO
167USD05/26/2023200DEFGLO167USD
189INR06/25/2023200123GLO189INR

 

Sheet2

 

entitycurrencydateamount Sourceconcatination(entity, currency)
167USD05/30/202350GLO167USD
167USD05/26/2023150GLO167USD
189INR06/25/202320GLO189INR
189INR06/27/2023180GLO189INR

 

Thansk,

4 REPLIES 4
lekshmirl
5 - Atom

Please use the attached WF for getting the results.

p-g
8 - Asteroid

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.

 

.Alteryx_Replace rows based on common source.png

 

Neeta_Latwal
7 - Meteor

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

p-g
8 - Asteroid

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.
Alteryx_Replace rows based on common source_workflow.png