Replace column data with a reference table

Question

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í:

idID Typenumbercategoryobject1CC1515212ABOX2CD1597536BTABLE3DE125763CKEY

reference table

Id_ReferenceId_codOLDNEW11CCCCR12CDCDR13DEDER21AAR22BBR23CCR

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

carlosreyesm326 · Answer

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

id_referenceID_codOLDNEW111112431334211322242335

carlosreyesm326 · Answer

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.

Maskell_Rascal · Answer

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

Find-Replace Workflow.yxmd