Joins In Alteryx

joins-in-alteryx

Today, we will see how to perform join operations using Alteryx. For this, I have 2 different tables in MySQL, say table_1 and table_2, as shown in below screenshot:

table-1

 

 

Img: Table_1

table-2

Img: Table_2

Alteryx Supports Inner Join, Left Join, Right Join, Full Join, and we will see how in below steps. We will join table_1 and table_2 on score_1 and score_2 fields respectively, which are basically integer fields in respective tables.

 

INNER JOIN:

Inner join in Alteryx is performed as shown in below screenshot:

inner-join-alteryx

Join Tool configuration will have score_1 = score_2 as shown in image.

 

 

 

 

 

after running the workflow, we will get output as:

inner-join-output-alteryx

 

LEFT JOIN:

Below image shows how to perform left join, we have Join Tool configuration as before, but here additional thing is the Union Tool.

left-join-alteryx

after running the workflow, it will produce below output

left-join-alteryx-output

 

LEFT EXCLUDING:

alteryx-left-excluding-join

and output will look like

alteryx-left-excluding-join-output

 

RIGHT JOIN:

Below image shows how to perform right join, we have Join Tool configuration as above, but again we need to use the Union Tool.

right-join-alteryx

after running the workflow, it will produce below output

right-join-alteryx-output

RIGHT EXCLUDING:

alteryx-right-excluding-join

and output will look like

alteryx-right-excluding-join-output

 

OUTER EXCLUDING

alteryx-outer-excluding-join

and output will look like

alteryx-outer-excluding-join-output

 

FULL OUTER JOIN:

alteryx-full-outer-join

output will look like below

alteryx-full-outer-join-output

 

IMPORTANT:

Above all joins we performed on and integer field, score_1 & score_2.Lets try to join on basis of any string/varchar field:

alteryx-inner-join-string-field

 

will output

alteryx-inner-join-varr-field-output

 

Even if we have matching emails, we got ZERO records after join.

If we want to join on a STRING field or varchar field efficiently, then we might need to take extra effort, for that we need to introduce FORMULA TOOL in which we will basically do a LOWERCASE() of string on which we want to join, observe the configurations below:

JOIN TOOL CONFIGURATION:

alteryx-join-configuration-varchar-field

FORMULA TOOL CONFIGURATION:

alteryx-formula-tool-config-1
AND

alteryx-formula-tool-config-2

Let’s do the INNER JOIN on varchar/string field now:

alteryx-inner-join-string-field

 

will output to

alteryx-inner-join-result-str

Now we got the desired result.

 

 

You may also like...