Skip to main content
Announcements
Global Transformation Awards! Applications are now open. Submit Entry
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

LEFT and RIGHT JOINS in Qlikview's Table View?

Hi

    I'm using ODBC to connect to one of my databases and I load two tables into my Qlikview document. I can see both tables in the "Table Viewer" and Qlikview has automatically done the JOIN between them. The problem is that it apparently made a natural join while I want it to be a Right Join. I cannot find the way to change that Join into a Right Join. I don't want to do a sql statement in the "Edit Script" option because I'll be adding more tables and the joins for those tables would some be Left, Right or natural joins so I would end up with a monster sql statement and I will end up seeing just one table in Qlikview, which I don't want.

    Does anyone know how to change the join type once the two tables are already loaded into Qlikview? I really appreciate it.

Thank You

Script to load the tables:

Tables joined in the Table Viewer:

5 Replies
oknotsen
Master III
Master III

The tables are not joined. If they would have been joined, you would have seen only one table.

What you see is that there is a relation between the two tables as they apparently share a field with the same name.

I notice that you are not using preceding loads. I strongly suggest you do so.

I also notice you do not give your tables names. I strongly suggest you do so, especially if you DO want to start joining tables when more developing your script or when you prefer more humanly acceptable names (instead of the terrible technical names they probably currently have). Add something like this in front of every SQL statement your currently have:

TableName:

You are absolutely right about not wanting to do much in the SQL statements. The more you can do in the Qlik script, the better (in most cases).

I do not see the problem with just seeing 1 table in QlikView. It might actually be better for performance.

If you only want to load values from the second table for those that have already keys in the first table, add this line:

where exists(WARRENTY_PERIOD)

to your SQL.

If you do end up wanting to join them, add one of these lines before your preceding load script:

left join(TableName)

right join(TableName)

inner join(TableName)

join(TableName) // for outer join

May you live in interesting times!
MarcoWedel

one implementation of Onno's suggestion could be:

tabWarrantyCodes:

SQL Select *

FROM DIM_WARRANTY_CODES;

tabWarrFriendlyDesc:

LOAD *

Where Exists(WARRANTY_PERIOD);

SQL Select *

FROM DIM_WARR_FRIENDLY_DESC

hope this helps

regards

Marco

Anonymous
Not applicable
Author

MyTable:

Load

*;

SQL Select

*

From MyLeftTable;

Right Join (MyTable)

Load

*;

SQL Select

*

From MyRightTable;

MarcoWedel

one example:

QlikCommunity_Thread_192236_Pic1.JPG

QlikCommunity_Thread_192236_Pic2.JPG

tabWarrFriendlyDesc:

LOAD * Inline [

WARRANTY_PERIOD, WARRANTY_CODE, WARRANTY_DESCRIPTION, WARRANTY_TYPE

3, code3, description3, type3

4, code4, description4, type4

5, code5, description5, type5

6, code6, description6, type6

7, code7, description7, type7

];

tabWarrantyCodes:

LOAD *

Where Exists(WARRANTY_PERIOD);

LOAD * Inline [

WARRANTY_PERIOD, WARRANTY_TABLE_DESCRIPTION

1, tabdescription1

2, tabdescription2

3, tabdescription3

4, tabdescription4

5, tabdescription5

6, tabdescription6

7, tabdescription7

8, tabdescription8

9, tabdescription9

10, tabdescription10

];

hope this helps

regards

Marco

MarcoWedel

You can also use "Keep" instead of "Join" like:

tabWarrantyCodes:

LOAD * Inline [

WARRANTY_PERIOD, WARRANTY_TABLE_DESCRIPTION

1, tabdescription1

2, tabdescription2

3, tabdescription3

4, tabdescription4

5, tabdescription5

6, tabdescription6

7, tabdescription7

8, tabdescription8

9, tabdescription9

10, tabdescription10

];

Right Keep (tabWarrantyCodes)

tabWarrFriendlyDesc:

LOAD * Inline [

WARRANTY_PERIOD, WARRANTY_CODE, WARRANTY_DESCRIPTION, WARRANTY_TYPE

3, code3, description3, type3

4, code4, description4, type4

5, code5, description5, type5

6, code6, description6, type6

7, code7, description7, type7

];

QlikCommunity_Thread_192236_Pic2.JPG

QlikCommunity_Thread_192236_Pic3.JPG

hope this helps

regards

Marco