topic Re: left join but using first table data in where clause? in QlikView

left join but using first table data in where clause?

ronaldwang — Wed, 25 Nov 2020 16:16:04 GMT

I want to use left join to obtain some information from second table, but I also want to use the column from the first table in the where clause, is it possible? By directly using it I got error message, just wondering how to get through this?

The syntax is as below:

TableF:
load Column1,
Column2,
Column3
resident Table1;

left join
load Column1,
sum(Column4) as Column5
Resident table2
where table2.column6>MakeDate(2014,11,21)

table2.column6<table1.column3
group by Column1;

Re: left join but using first table data in where clause?

avinashelite — Fri, 24 Nov 2017 05:42:35 GMT

the best approach is to use the mapping tables and map required columns from Table1 in Table2 then use the where clause and group by , will give you the results

Re: left join but using first table data in where clause?

Anil_Babu_Samineni — Fri, 24 Nov 2017 06:06:03 GMT

that means, In Table2 you have some Qualify Statement? Isn't it

Re: left join but using first table data in where clause?

ronaldwang — Mon, 27 Nov 2017 03:03:19 GMT

no, I am not using qualify statement. the syntax just to show which table I am referring to.

Re: left join but using first table data in where clause?

ronaldwang — Mon, 27 Nov 2017 03:04:11 GMT

thanks, do you have link to any examples?

Re: left join but using first table data in where clause?

Anil_Babu_Samineni — Mon, 27 Nov 2017 03:11:51 GMT

To get alias names like Table2, You must need Qualify statement to bifurcate different level on condition

Re: left join but using first table data in where clause?

ronaldwang — Mon, 27 Nov 2017 04:11:09 GMT

Hi, Yes, even I have use qualify statement and being able to use table2.*, I still won't be able to refer back to table1 column from the left join statement.

Re: left join but using first table data in where clause?

Anil_Babu_Samineni — Mon, 27 Nov 2017 05:24:56 GMT

What about this?

Table1:

Load Column1, Column2, Column3 From T1;

Qualify *;

Table2:

Load Column1, Column4, Column3, Column6

TableF:

load Column1,

Column2,

Column3

resident Table1;

left join (TableF)

load Table2.Column1,

sum(Table2.Column4) as Column5

Resident table2

where table2.column6>MakeDate(2014,11,21)

table2.column6<table1.column3

group by Column1;