Match file inputs by specific field

[aviziteu]

Hi,

I've read few other articles on the matter and still could not make it for my case, so please help me with this:

i have 2 input files and they match by field:

input 1: LE

input 2: ID

I need in the end to fill in the blanks on Country with the Names found in input 2 ( based on the above criteria LE with ID), in my case for LE 4 France to be populated in Input 1.

Thanks!

[atcodedog05]

Hi @aviziteu 

You can use Join tool 

https://help.alteryx.com/current/designer/join-tool

or 

Find and replace tool

https://help.alteryx.com/current/designer/find-replace-tool

Examples workflows attached 

Hope this helps 🙂

If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

[JosephSerpis]

Hi @aviziteu I mocked up a workflow that produces the output you describe let me know what you think?

[aviziteu]

@JosephSerpis , thank you, it helped me so much to understand the logic behind Join! it is the first time I use it.

It works, but I wish to add something, there are also empty fields and it all needs to be displayed in the end, not only the intersection ( including the row in orange which has empty LE but has the attribute case "E"). 

Could you advise if Union is needed?

THANKS~ 🙂

[atcodedog05]

Hi @aviziteu 

Yes adding union tool like this would help that task.

Workflow:

[JosephSerpis · Accepted solution]

Hi @aviziteu you are correct a Union is needed I amended the workflow to show how to deal with this. Let me know what you think?

[atcodedog05]

Hi @aviziteu 

Alternatively i would suggest Find&Replace tool since it over comes one-many joins(happens if join columns are not unique in both data source) and always maintains the sort order.

Output:

Workflow:

For understanding one-to-many join issue look at this workflow

Find and Replace works only on string field hence need to use extra select tools to convert to string

Hope this helps 🙂

[aviziteu]

Thanks a lot, I am so happy with it! @JosephSerpis 

[aviziteu]

Thank you @atcodedog05 , this is an interesting idea, I will certainly test it!