Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. $300 savings extended to February 9th Learn More
Highlighted
greend21
Contributor II

Inner Join before Left Join?

Currently, I am loading table one and then left joining table two to table one. Now what I need to do is load table one, load table two, inner join table two to a table three(which is a load of a subset of table two), and then left join this back to table one. How would I best put this in the script?

1 Solution

Accepted Solutions
jensmunnichs
Contributor III

Re: Inner Join before Left Join?

If table3 is based on table2, maybe you could solve this using a preceding load?

Table1:

LOAD fields

FROM source

Left join (Table1)

Table2and3:

LOAD *,

     Calculation(field1) as CalcField1,

     Calculation(field2) as CalcField2,

     Etc..;

LOAD field1, field2

FROM <source>;

Or, if you don't want to do that, you could Load table2, load and inner join table3, then load and right join table1.

View solution in original post

5 Replies

Re: Inner Join before Left Join?

Hi Dan,

Maybe something like :

Table1:

LOAD *

From Table1;


Table3:

LOAD *

Where...

From Table2;

inner join (Table3)

LOAD *

From Table2;

left join(Table1)

LOAD *

Resident Table3;

Drop table Table3;

I think that's quite performing.

Best regards,

Marina

jensmunnichs
Contributor III

Re: Inner Join before Left Join?

If table3 is based on table2, maybe you could solve this using a preceding load?

Table1:

LOAD fields

FROM source

Left join (Table1)

Table2and3:

LOAD *,

     Calculation(field1) as CalcField1,

     Calculation(field2) as CalcField2,

     Etc..;

LOAD field1, field2

FROM <source>;

Or, if you don't want to do that, you could Load table2, load and inner join table3, then load and right join table1.

View solution in original post

greend21
Contributor II

Re: Inner Join before Left Join?

The right join seems to be the simplest method.

I noticed if I said Right Join(Table1) it said Table1 was not found. It would have to look like this for the loads and if I want to rename the table right?

Table2:

Load *

From source;

Inner Join(Table2)

Table3:

Load *

From source;

Right Join (Table2)

Table1:

Load *

From Source;

Rename Table Table2 to Table1;

jensmunnichs
Contributor III

Re: Inner Join before Left Join?

Yes, sorry I don't think what I wrote was very clear. What I meant in the last sentence was 'then load and right join table1 (to table2)' so Right Join (Table2) is correct. Right Join (Table1) doesn't work because Table1 hasn't been loaded yet at that point.

Also yes your renaming statement seems correct as well. Have you tried this script yet to see if it works?

greend21
Contributor II

Re: Inner Join before Left Join?

The script works. I was just about to validate the data still looked as expected from the joins. Thanks!