Compare attributes to write in the right category

[sfrank5]

Hello everyone,

I just started working with Alteryx, so I am really a beginner and need some help. 

I have a data set that contains a taxonomy (multiple attributes in an inconsistent order) separated by "/" and "+". I have already divided these into individual columns in individual columns using the Text to Columns tool (Taxonomy1-Taxonomy4).

I have another table in which each attribute is assigned to a category.

I have already attached these categories as columns.

Now I would like to transfer the individual attributes to the correct category columns. How do I solve this problem?

I hope I expressed my problem clearly and you can help me. Thanks in advance! 

[PhilipMannering]

Hi @sfrank5. I'm pretty certain what you're asking for can be done. But to help, could you provide some sample data with expected output?

[AngelosPachis · Accepted solution]

Hi @sfrank5 ,

The first step I would take is to convert the taxonomy table in a thin and long format via using a "Transpose" tool. Before doing that, I would also add a "Record ID" tool to be able to track the different records. The output of the transpose tool would look something like that:

Bringing the table in this thin and long format allows us to make use of the "Find Replace" tool. Essentially we would look in the "Value" column above and try to find values of the Taxonomy column from the second table (S, ME and so on). If this value is found, we can append the correct column (MTL1,2 etc).

Then the penultimate step would be to bring our table back to the original format. We can do that via a "Cross Tab" Tool. Now for each of my original records, I have all the attributes transferred in the correct columns. 

You might be able to notice that I only have MAT_L1, but that's because I've used a mock data set. For your occasion, you will be able to see more columns there. Also I have a null column, denoting attributes that have not been matched to a certain column. You may/may not have this column in your dataset.

Final step is to bring the data back into our main table, via using a Join tool and joining on the record id

Hope that helps, please let me know if you have any questions. You can also find the workflow I've created attached.

Regards,

Angelos

[sfrank5]

Thanks a lot, that is exactly what I needed!