Qlik Community

QlikView Documents

Documents for QlikView related information.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More

Different Join Functions in Qlikview

Different Join Functions in Qlikview

In join functionality we have four type of the joins as we know and at the place they differently used in the data model. I provide this document for under stand the functionality of the joins in the 4 ways.

There are 4 type of joins

1. Left Join

2. Right Join

3. Inner Join

4. Outer Join

1. Left Join :- When we use this join between two tables the join occurs but the rows from the left table taken and from right table only matching rows.

2. Right Join:- When Right Join used between tables so in this only right tables rows taken and the matching rows from the left table taken.

3. Inner Join:- In this join type only matching rows from both the tables right and left table is taken.

4. Outer Join:- In this join rows taken which is not match another table.

Two Tables.png

After different Joins performance table look with different joins functionality and the number of rows is different in each tables

Different Joins.png

You can check the attached file also and go for loading different joins table for its definition at a time only one join. In the script vDefine variable is always set to 1 and for viewing different join table then set 1 for that tab variable and rest are must set as 0 in the variable. At a single time if you view only one table will give you best definition of the joins and you can see the join performs and the result in the table box for rows that loaded.

LET vDefine = 1;
LET vLeftActivator = 1;
LET vRightActivator = 0;
LET vInnerActivator = 0;
LET vOuterActivator = 0;


Regards

Anand

Attachments
Comments
Not applicable

Thanks. anyone can understand this easily.

Thanks are you right that's why i provide this document.

Not applicable

Good explanation, Very easy way to understand joins.

0 Likes
SunilChauhan
Esteemed Contributor

Nice post Anand .

what are situation where we can use join and when we can use Keep.

Thanks

0 Likes
Not applicable

Hi All,

@sunil kumar please note

keep and join gives the same output,only diff is keep keeps the tables separated in data model, where as join merges the tables that are joined.

Tx

Hari

0 Likes
SunilChauhan
Esteemed Contributor

Thanks Harikrishna,

I know in Keep have separate table but work as join. i want to know what are the scenario we can use Keep and join.

0 Likes
Not applicable

pls explain other joins.in interview they asking  other joins except (outer,left,inner,right)

0 Likes

This are the main joins except this Keep is used with the joins, Basically keep is used for reduced the data when you use.

Ex:-

If you use Left Keep join functionality the join is perform but you get two tables with left table data only in the table similarly other joins is perform.

0 Likes
happydays1967
Contributor

As I found out through trial and err, it is very important to know the order in which QlikView evaluates joins and 'unions' or concatenation as it is called in QlikView.

For example, we load a table with a join as described above, for instance an outer join:

SomeTable:

LOAD

    KeyID,

    Value

FROM

    SomeDataSource;

LEFT JOIN LOAD

    KeyID,

    AdditionalValue

FROM

    AnotherDataSource;

This will give us;

KeyID|Value|AdditionalValue

However, say we have to add records with similar values and fieldnames, bu from a different datasource (or add dummy records or you think up the scenario) and I would do the following:

SomeTable:

LOAD

    KeyID,

    Value

FROM

    SomeDataSource;

LEFT JOIN LOAD

    KeyID,

    AdditionalValue

FROM

    AnotherDataSource;

LOAD

    KeyID,

    Value

FROM

    DataSourceNr3;

LEFT JOIN LOAD

    KeyID,

    AdditionalValue

FROM

    DataDataSourceNr4;


This will create two identical tables linked through a synthetic-key, something that usually should be avoided at all cost. This is apparently because Qlikview first tries to concatenate the first part of the second load, and then afterwards performs the last join. It does not recognize you want to create two equally shaped datasets that you then want to concatenate as a whole.

The way I go around doing this is explicitly naming the second load and suffixing the fields with a number. Then load * from the two tables using concatenate to make shure QlikView dowsn't stumble on the different fieldnames.

Maybe this is not fully on-topic to this subject, but it is something that made me lose a lot of time and caused me many a headache until I figured out the order in which joins and concats (whether explicit or not) were carried out.

Hope this will save someone else the headache and time

Regards,

HP

0 Likes

Thanks Hans,

Better if we can maintain single table where ever is possible and try to join it with proper Key field which is unique in nature what ever if it is composite keys or autonumber keys. If we get single table it is easier for the reporting or to create another analytic reports. Thanks for explanation of joins.

Regards

Anand

0 Likes
Version history
Revision #:
1 of 1
Last update:
‎2014-11-27 02:00 PM
Updated by: