JOINS in Qlikview

[srishsum2017]

• The JOIN statement is a prefix to the LOAD statement. It is used to join the table that is being loaded to a previously loaded table.
• The two tables are joined using a natural join, this means that the columns in both tables are compared and the join is made over those columns that have the same column names. This means that if multiple columns are shared between tables, the match will be made over the distinct combinations of those columns.
• By default, QlikView performs an outer join. However, in SQL by default join is inner join.

There are 4 type of joins-

1. Left Join
2. Right Join
3. Inner Join
4. Outer Join

Left Join- All rows from the first table and those rows from the second table that have a corresponding key in the first table, will be included in the result. When no match is found, null values will be shown for the columns that are unique to the second table.

Right Join- All rows from the second table and those rows from the first table which have a corresponding key in the second table, will be included in the result. When no match is found, null values will be shown for the columns that are unique to the first table.

Inner Join- Only rows that can be matched between both tables will be kept in the result.

Outer Join- All rows will be kept in the result, rows that do not have a corresponding value in the other table will get null values for the fields that are unique to that table. When no prefix is specified, this is the default join type that will be used.

For Example-

Suppose there are 2 tables

Table1:
LOAD * Inline
[
Key, ColA
1,R1
2,R2
3,R3
];

Table2:
LOAD * Inline
[
Key, ColC
1,F1
2,F2
4,F4
];

[alex00321 · Accepted solution]

Nice share！