Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Joining data from unfixed column locations

rjavier
5 - Atom

Hi,

 

I need help on joining tables where the look-up values are not set in any specific columns, but could be anywhere. How do I join using non-key columns between two tables?

 

Table 1 is the driving factor table:

clipboard_image_0.png

 

Table 2 is where I have to look up the values, but could land in any column:

clipboard_image_1.png

 

Table 1 Row 1 can be joined with Table 2 via Column 1 and Column 3

   Row 2 can be joined with Table 2 via Column 1, Column 3, and Column 6

   Row 2 can also be joined with Table 2 via Column 2, Column 4, and Column 5

   Row 3 can be joined with Table 2 via Column 1, Column 6, and Column 7

 

The desired result is:

clipboard_image_2.png

3 REPLIES 3
BrandonB
Alteryx
Alteryx
You probably will want to use a transpose tool on each table and select the table key as a key field in both tools. Then feed the outputs of both transpose tools into a join tool where you join on the value fields. The inner join will show you all of the values that match between the two keys which can then be used downstream in your process.
Martyn
9 - Comet

Transpose and join is definitely the way forward for something like this -

 

clipboard_image_0.png

 

The summary tools are used to concatenate all of the column values (grouped by key) to ensure that the result only contains record where there was a complete join (i.e. all populated columns from table 1 were found in columns in table 2).

 

This works from the data you have given.

rjavier
5 - Atom

I got stuck after joining the transposed inputs. I didn't know how to loop back the main table. Thank you very much for your help.

Labels