Inner & Left Join/Keep

[Anonymous]

Hi All,

I have a below sql query. Could someone please explain how I can correctly write this in QlikView? I am confused because 'product' table needs to be joined using 2 keys(CustomerID & ProductID). Assume CustomerID is an integer and ProductID is a varchar field. Also, since join makes a table look like one with all the combined fields in table viewer, do you recommend using keep function instead? If I use keep, do I need to drop the fields and table at the end? If so, could someone please provide the best solution to answer all the questions using the below query to convert it into a QlikView code. Thank you.

select a.Name, b.CustomerID, c.ProductID

from data a

inner join customer b

on a.CustomerID=b.CustomerID

left join product c

on c.CustomerID=b.CustomerID and c.ProductID=b.ProductID

where a.Name='James'

and b.CustomerID='1001'

and c.ProductID='501'

[marcus_sommer]

Maybe my suggestion from Subqueries - script‌ might be useful here, too.

- Marcus

[maxgro]

there are many different ways, 2 ways are

- you can use your sql query in QlikView without any changes

SQL select a.Name, .......

- you can load the 3 tables without any joins and Qlik will associate (by name) the tables

data:

SQL select Name, ....

from data;

customer:

SQL select .....

from customer;

product:

SQL select ....

from product;

then add some listbox (Name, CustomerID, ProductID) to filter the rows you want

[Anonymous]

I thought about autonumber option to combine the two primary keys. However, does that work with one field being an integer and one being a varchar? I'll try different options including the autonumber.

[marcus_sommer]

You need not to worry about data-types within qlikview: Data Types in QlikView.

- Marcus

[Anonymous]

Yeah, I always preferred to load all the tables using the sql script then just format the columns in qlikview. However, I just wanted to understand how to do it in qlikview. I don't like assuming the result being correct from having qlikview using the automatic association. Automatic association in qlikview does full outer join and that is not what I want. I can load tables separately like u mentioned but I wasn't sure how to link the tables when there are two keys to link the tables.

[maxgro]

I can load tables separately like u mentioned but I wasn't sure how to link the tables when there are two keys to link the tables

If the 2 fields (keys) have the same name, QlikView makes a synthetic key; this isn't a problem, your db model still works, but if you still want to remove the syn key you can use autonumber or concat the 2 fields in one

Synthetic Keys‌

[Anonymous]

Hi Massimo! I had the 2 keys named the same and it did not create a synthetic key because the two table were inner joined. I looked at the table viewer and it only displayed one table with the desired fields all in that table. Thanks.