topic Re: LEFT and RIGHT JOINS in Qlikview's Table View? in QlikView

LEFT and RIGHT JOINS in Qlikview's Table View?

Fri, 20 Nov 2020 18:02:30 GMT

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:

Re: LEFT and RIGHT JOINS in Qlikview's Table View?

oknotsen — Thu, 19 Nov 2015 23:45:59 GMT

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

Re: LEFT and RIGHT JOINS in Qlikview's Table View?

MarcoWedel — Fri, 20 Nov 2015 00:36:01 GMT

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

Re: LEFT and RIGHT JOINS in Qlikview's Table View?

Anonymous — Fri, 20 Nov 2015 00:37:07 GMT

MyTable:

Load

SQL Select

From MyLeftTable;

Right Join (MyTable)

Load

SQL Select

From MyRightTable;

Re: LEFT and RIGHT JOINS in Qlikview's Table View?

MarcoWedel — Fri, 20 Nov 2015 00:46:25 GMT

one example:

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

Re: LEFT and RIGHT JOINS in Qlikview's Table View?

MarcoWedel — Fri, 20 Nov 2015 00:52:15 GMT

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

];

hope this helps

regards

Marco