topic Re: Joining tables in QlikView

Joining tables

Wed, 15 Jun 2016 21:11:29 GMT

I have three tables t1, t2 and t3 with keys k1, k1+k2 and k2 respectively. (Essentially, t2 connects t1 and t3) I need to filter the records from t2 which match the key values in t1 and then join the resultant to t3. Can any one suggest the proper join conditions?

If I use left join with t2 as the main table, I end up picking extra values from t1 and if I use t1 as the main table then there is no way for me to get data from t3.

Re: Joining tables

swuehl — Wed, 15 Jun 2016 21:56:04 GMT

Not really sure, so if this is not what you want, could you post few lines of input table records and your expected result table?

t1:

LOAD k1 FROM t1;

t2:

LEFT KEEP

LOAD k1,k2 FROM t2;

JOIN

LOAD k2 FROM t3;

DROP TABLE t1;

Re: Joining tables

Thu, 16 Jun 2016 14:37:16 GMT

How do I join t3 because there is no t2 once I left join it.I get error saying table not found. Do I join it to t1?

Also, my tables have other data as well and not just the keys!

Re: Joining tables

swuehl — Thu, 16 Jun 2016 15:20:38 GMT

No, I did a LEFT KEEP

Re: Joining tables

Thu, 16 Jun 2016 15:29:17 GMT

Aah.. I was under the impression that I have to specify the table name when I join the two tables. but even without specifying table names, Qlik automatically identifies association based on column names.

Thank you Swuehl! Keep is a Keeper lol never understood the use of it till now.

I also found this helpful after reading your post.

https://help.qlik.com/en-US/sense/2.2/Subsystems/Hub/Content/Scripting/combine-tables-join-keep.htm

Re: Joining tables

swuehl — Thu, 16 Jun 2016 15:35:20 GMT

The JOIN / KEEP is performed to the table preceding the current LOAD statement in the script, if you don't sepcify a table name (which would be more safe, advisable).

It is not searching your data model for possible matching key fields, if that is what you are assuming.