LEFT and RIGHT JOINS in Qlikview's Table View?

[#1]

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:

[oknotsen]

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

[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]

MyTable:

Load

*;

SQL Select

*

From MyLeftTable;

Right Join (MyTable)

Load

*;

SQL Select

*

From MyRightTable;

[MarcoWedel]

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

[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
];

hope this helps

regards

Marco