Replace column data with a reference table

[carlosreyesm326]

Hi everyone

Estoy tratando de reemplazar los identificadores de algunos tipos de objeto de acuerdo con una tabla referencial, no puedo usar join porque tengo varios tipos de identificadores en la misma tabla, los primeros 13 registros son de tipo de identificación y los últimos 5 son de tipo categoría, sería algo así:

id	ID Type	number	category	object
1	CC	1515212	A	BOX
2	CD	1597536	B	TABLE
3	DE	125763	C	KEY

reference table

Id_Reference	Id_cod	OLD	NEW
1	1	CC	CCR
1	2	CD	CDR
1	3	DE	DER
2	1	A	AR
2	2	B	BR
2	3	C	CR

As you can see, different column values share the same table. I need that where this "CC" is replaced by "CCR" (These values are for example), I try to do it by a filter and a join, but the problem is that there can be N Id_Reference, so it could not be applied, also with a Multi-field macro, but I can't use [_currentfield_] and [_Currentfieldname_] in the same regular expression, I'm new to alteryx, I would like to know if someone with more experience knows how I can achieve this, Sorry about my English, it's not my language maternal

[Maskell_Rascal]

Hi @carlosreyesm326 

It sounds like a job for the Find Replace tool. Based on your example data for updating the ID Type and category columns, the below should work for you. 

I've attached a sample workflow for you to try out. 

If this solves your issue please mark the answer as correct, if not let me know!

Thanks!

Phil

[carlosreyesm326]

Yes, that's what I'm looking for but dynamically, I enter a json with N columns to replace, after parsing, the result is the reference table, but I cannot do a search and replace for each column, since I do not know how many I have to replace. it depends on the JSON, if you bring a single object then I just replace one column, the column that the json brings
Since the idea is to use it in different reports.

[carlosreyesm326]

I forgot to mention that the values are referential numerical values, (0, 1. 2. 3), for which sometimes they are repeated

id_reference	ID_cod	OLD	NEW
1	1	1	1
1	2	4	3
1	3	3	4
2	1	1	3
2	2	2	4
2	3	3	5