How to search/replace a column based on another table ?

AmineCher
Level 1
How to search/replace a column based on another table ?

Hello,

 

I'm looking for a way to do a search/replace on a column based on a reference table that contains two columns:

Refrence_Table (WrongValueToMatch, Replace)

Table_Data (Col1, Col2, Col3, Col4)

 

I want to do a search and replace on "Col1" of the Table_Data table, I'll have to search for the values of the "WrongValueToMatch" column and if this value is found, I'll have to replace it in "Col1" with the value of the "Replace" column.

I'm looking to replace the character, not necessarily the exact value, for example :

WrongValueToMatch | Replace

é | e

and in Col1 I have "étoile" as the value, so I expect to have the value "etoile".

Thanks!

0 Kudos
1 Reply
AlexT
Dataiker

Hi,
You should be able to use a Join recipe and a Prepare recipe to achieve this.

https://academy.dataiku.com/excel-to-dataiku-dss-quick-start/858785

So you could do a left join Table_Data col1 on ref table  WrongValueToMatch and keep the "Replace" column, creating a new column with all the replace values.

In prepare recipe  https://doc.dataiku.com/dss/latest/preparation/processors/create-if-then-else.html in a prepare recipe and replace Col1 with replace value if it exists.

Let us know if this helps.

Thanks

0 Kudos