Join on 2 different columns

[faiqz]

Hi,

I have a problem where I have to join 2 different datasets on 2 columns. 

1. First dataset have structure:

name

id

2. second dataset's structure:

name

new_id

old_id

I want to join on

name=name 

and id=new_id

or

name=name

and id=old_id

please comment below if you have any solutions

[ncrlelia]

Hi @faiqz,

You can perform JOIN twice. First JOIN based on name=name and id=new_id, then JOIN those records which are not matched to the 2nd dataset again based on name=name and id=old_id.

Hope this helps.

Best Regards,

Lelia

[faiqz]

hi @ncrlelia 

oh I see. Is that the only solution for now?

[Zie86]

I think that is the correct process if need to do JOIN in Alteryx. As long as you have defined the filed name to use on the JOIN fields.

[ncrlelia]

Hi @faiqz,

The alternative is JOIN on name=name. Then use a filter to keep only those records whose id = new_id OR id=old_id.

This method will not be ideal if you datasets get too large and have multiple of the same names, as the JOIN will create a larger than needed dataset (before filtering) with redundant rows, which in turn affects the performance of the workflow.

Cheers,

Lelia

[Zie86]

If you just want the filter with the same ID, should use ID = old_id, then select only Name to show as fields when selecting the Input under Join options and add Browse to see the result.

I have provided some examples based on your input.