topic Re: Help with Joins? in QlikView

Help with Joins?

Anonymous — Wed, 25 Feb 2015 17:18:56 GMT

Looking for some help...I have two tables, on tab 1- Final and tab 2 -Pivot I would like to left join the Pivot table to the High table. My syntax on the 3rd tabe looks like this...

LEFT JOIN (Pivot)

LOAD *

FROM Final;

Re: Help with Joins?

Anonymous — Wed, 25 Feb 2015 17:21:54 GMT

Run in Debug Mode.

Put full Edit Script source..

Re: Help with Joins?

MK_QSL — Wed, 25 Feb 2015 17:28:38 GMT

If you have already loaded both tables... use below

LEFT JOIN (Pivot)

LOAD * Resident Final;

Drop Table Final;

Re: Help with Joins?

oknotsen — Wed, 25 Feb 2015 17:31:53 GMT

So your first table is called "Final" and your 2nd table is called "Pivot"?

And your table called "Pivot" should be the leading table?

You also do not want to change the order in which the are loaded?

When loading "Pivot", try this:

Pivot:

right join(Final)

Load *

from source;

This effectively is a "left join Pivot". The result table is called "Final". To change that, do this:

rename table Final to Pivot;

Re: Help with Joins?

Wed, 25 Feb 2015 17:50:54 GMT

Hi John,

If you have already loaded both the tables into datamodel than use as per Manish explained. However it is not required to load both of the tables seperately into datamodel if they are finally joined in datamodel.

You can save the loading time of one table as below:

You can load first Pivot table using your normal load statement and then load Final table using the below syntex.

(Assuming your final table is in excel)

Left Join(Pivot)

Load <Fieldname1>,<Fieldname2>... from Final.xlsx((ooxml, no labels, table is Sheet1);

Just remember that Qlikview join with same Fieldname so you need to rename the joining fields as per Pivot table.

You can also create a field by concatenating all joining fields or using "autonumberhash" function. You need to create this in both of the tables(Pivot and Final). You may need preceding load to do that. This will improve the performance of load if both of tables are very huge.

Hope this helps,

Anosh

Re: Help with Joins?

Anonymous — Wed, 25 Feb 2015 18:24:54 GMT

I figured it out, thank you for your feedback and help. I was attempting to create a 3rd table that combines Pivot and Final, guess that was not needed

Inner Join (Pivot)

Load *

Rename Table Pivot to Master