Join 2 columns based on dynamic keywords

[rajdaiya]

Hi community,

I am fairly new to Alteryx and am struggling with a problem statement. I want to join columns based on the specific keyword ( generated dynamically by a series of filters).

For example:

Lets say my Table A has static column names (these column names dont change and are pre-written) : Date | US Skin Dependant | US Face Dependant | US Hair Dependant | US Body Dependant

and my Table B dynamically generated the column names:  Date | Skin A | Face A | Hair B

I want to join these 2 tables using the Date field (which is common) and ensure row values from Skin A get written into US Skin dependant, Similarly row values from Face A get populated within US Face Dependant and Hair B within US Hair Dependant. How should I proceed? Please help me out

[Qiu]

@rajdaiya 
How daynamic is your Table B column names?

like it alwasys contains some key words, such as "Skin", "Face"?

[rajdaiya]

Hi @Qiu 

Yes it will always contain one keyword like "hair" "skin" "body", etc which is present in Table A

[mceleavey]

@rajdaiya ,

will it always be in the same order?

As in, will the second column in table B be the same the second column in Table A?

M.

[rajdaiya]

Hi @mceleavey 

No. So the 4th column in Table A could be skin whereas 3rd column in Table A will be Skin. However, the position of the KW in the column anme will always be same. So 3rd keyword in columns from Table A will be Skin, Body, Hair whereas 2nc keyword in columns from Table B will be Ski, Body, or Hair

[mceleavey]

Hi @rajdaiya ,

ok, I've attached the workflow.

Basically, I used a mapping table of keywords with the Find and Replace tool with an append function.

This allows Table B to be mapped to table A using the key words once the columns have been transposed.

Then it's just a case of replacing the value in A with the values in B and cross-tabbing back.

Hope this helps,

M.