topic Re: Inner & Left Join/Keep in QlikView

Inner & Left Join/Keep

Anonymous — Sun, 30 Aug 2015 16:33:04 GMT

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'

Re: Inner & Left Join/Keep

marcus_sommer — Sun, 30 Aug 2015 16:41:15 GMT

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

- Marcus

Re: Inner & Left Join/Keep

maxgro — Sun, 30 Aug 2015 17:01:04 GMT

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

Re: Inner & Left Join/Keep

Anonymous — Sun, 30 Aug 2015 18:19:15 GMT

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.

Re: Inner & Left Join/Keep

marcus_sommer — Sun, 30 Aug 2015 18:25:50 GMT

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

- Marcus

Re: Inner & Left Join/Keep

Anonymous — Sun, 30 Aug 2015 18:28:20 GMT

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.

Re: Inner & Left Join/Keep

maxgro — Sun, 30 Aug 2015 20:50:28 GMT

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‌

Re: Inner & Left Join/Keep

Anonymous — Tue, 01 Sep 2015 01:15:04 GMT

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.