Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
greend21
Creator III
Creator III

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
Creator III
Creator III

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
marinadorcassio1
Partner - Creator
Partner - Creator

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
Creator III
Creator III

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.

greend21
Creator III
Creator III
Author

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
Creator III
Creator III

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
Creator III
Creator III
Author

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