Match different names to same ID in different datasource

[Susanlu]

Hi everyone,

I'm currently running into a situation in Alteryx macro, in order to create a macro for repetetive data cleansing.

I have multiple data sources, and I would like to categorize "Identification number", "document number", "drivers licence", etc, in each different data course all as "ID".

So it would be like: from data source 1, if there are names "Identification number", it falls under "ID" category, and when I change/connect to a different data source, "document number" also falls under "ID". So I can add steps for further cleansing rules specifically for ID afterwards.

Since it's build in Alteryx macro, I hope there would be a way that I don't need to change the filter/formula every time I connect with a different data source (if possible).

Not sure if I have made myself clear. 

Thank you in advance for your help!

[atcodedog05]

Hi @Susanlu 

Here is what you can do. You can use dynamic rename tool to rename the columns to ID with use of a rename list.

Workflow:

In the above configuration, I am passing the list of names that need to renamed to ID. This way each iteration it will be renamed to ID.

Hope this helps 🙂

[Susanlu]

Hi - thank you for your reply!

Would there be a way that I don't need to rename everything, but can still categorize them into the ID filed? 

so individually, I can do: REGEX_Match ('ID') THEN 'Identification Number' 

  - that means we use the abbreviation 'ID' in a different column in excel beside 'Identification Number', same as 'drivers licence' etc, so if they found 'ID' it would be categorized into 'ID'

but I do need to change the later part to 'drivers licence', etc, for different data source.

[atcodedog05]

Hi @Susanlu 

You can do something like this. What I am trying to do is make a copy of the needed column and rename it to ID.

Workflow:

1. Using dynamic select tool selecting only the ID columns.

2. Using dynamic rename tool to rename them to ID. This is the copy of the column created and renamed to ID.

3. Using join tool positional join. And joining the new ID column with the rest of the data.

Hope this helps 🙂

[Susanlu]

Thank you! I will try tomorrow and see if it works! (it sounds your solution fits well with my concerns :p)