Find and replace function in Alteryx -How it can be done in Azure Data Flow

I have a "Find and replace " tool in Alteryx which finds the Col value of csv file1 and replaces it with the look up csv file2 which has 2 columns like Word and ReplacementWord.

Example : Address is a col in Csv file1 which has value like St.Xyz,NY,100067

And Csv file 2 has Word ReplacementWord NY NewYork ZBW Zimbawe etc....

Now the final Output should be Address St.Xyz,NewYork,100067

Please help guys .

Hey here's the problem .I have a "Find and replace " tool in Alteryx which finds the Col value of csv file1 and replaces it with the look up csv file2 which has 2 columns like

Word and ReplacementWord.

Example :

Address is a col in Csv file1 which has value like St.Xyz,NY,100067

And Csv file 2 has

Word ReplacementWord

NY NewYork

ZBW Zimbawe etc....

Now the final Output should be

Address

St.Xyz,NewYork,100067

Please help guys .

1 Answer

I tried to reproduce your scenario in my environment to achieve the desired output I Followed below steps:

• In dataflow activity I took 2 Sources Source 1 is the file which contain the actual address. Source 2 is the file which contain the country codes with names.

• After that I took lookup to merge files based on the country code. In lookup condition I provided split(Address,',')[2] to split the address string with comma and get the 2nd value from it Which will be the country code based on this : Xyz,NY,100067 and column_1 of 2nd source. Lookup data preview:

• Now took Derived Column and gave column name as Address with the expression replace(Address, split(Address,',')[2], Column_2) It will replace the What we split in lookup from Address string to value of Column_2 Derived column preview:

• then took select and deleted the unwanted columns Select Preview:

• now providing this to sink dataset

Output