Dynamic join based on values in a table

[byung0917]

Okay. This has been very challenging for me and keeping my fingers crossed hoping you can help me out with genius mind. 

Here is what I need to do. 

I have two tables to join

Table A

ID	Column A	Column B	Column C	Column D	Column E	Column F
a	15	62	82	80	FALSE	54
b	16	63	62	80	TRUE	55
c	3	88	56	83	FALSE	54
d	4	62	56	83	FALSE	62

Table B

ID	Column A	Column B	Column C	Column D	Column E	Column F
A	*	62	82	80	*	54
B	16	63	62	*	*	55
C	4	*	*	83	FALSE	*
D	*	88	*	83	FALSE	54

So, a should be joined with A

b should be joined with B

c should be joined with D

d should be joined with C

So, the tricky thing about this join is.. the wildcard * can pop up any column in the table B

If wildcard is in a column, I shouldn't use that column for join. 

Based on what I have in a row of table B, I need to change columns to join. 

Is it possible? 

Since I have over hundreds thousands of rows in both table A and B,

I was thinking about categorizing rows of the table B like...

WCCCWC

CCCWWC

CWWCCW

WCWCCC

W : wildcard

C : String

and feed this into macro which changes joining columns... is it possible??

Please help!

[kathleenmonks]

Hi @byung0917 

I would create a new ID column for Table B that checks if one of the other columns contains a * and if so, the new ID would be 'A*' or something and if not, the new ID would remain the same as the old ID. Then join Table B with Table A on Table A_ID = TableB_newID.

Hopefully that makes sense! Happy to put that into a workflow for you to explain it in more detail. 

[JReid]

Hello @byung0917 

One of the shortcomings of the Alteryx Join tool is it doesn't allow calculated joins like SQL or other programs like Python or R. If you can do it in one of those tools it may be preferable. There's two ways I can think of doing this within Alteryx:

1. Brute force it
   
   1. Append Fields instead of join and have a filter afterward with your join logic ((A='*' OR A=a) AND (B='*' OR B =b) AND...)
   2. This would drastically increase the size of your data set and likely be very slow
2. Batch Macro it
   1. Use the logic you had set forth in your proposal with a C/W dataframe
   2. Have a summarize tool batch the orders through your macro
   3. Use a Multi-field formula tool with your batches to turn field from table A into *
   4. Join

[Martyn]

Try transposing both tables and then join on ID and Name.

Follow with a summarise tool to count distinct Name (column name) for each combination of IDs.

From your transpose of Table B - count how many non * columns there are (using a summarise tool).

Follow with a join to match these the previous summarise tool (joining on Table B ID and count).

This will give you the combinations that join between Table A and Table B.

[terry10]

Simple solution. - See attached workflow.

1. Transpose table B
2. Filter out ID's that shouldn't be used in the join due to wildcard
3. Crosstab table B
4. Join to table A

 P.S.  Don't be concerned about transposing very large data sets; it's not a problem.