How to apply condition to Left Join?

[#1]

Hi, All

How to make left join with condition?

for example, i can write in PL/SQL

select Table1.ProductID, value1.Table1, value2.Table2

from Table1

left join Table2 on (Table1.ProductID=Table2.ProductID and value2.Table2 < value1.Table1)

how to make something of the kind in QlikView? i.e. how to apply condition value2.Table2 < value1.Table1 to left join in QlikView?

[preminqlik]

use applymap

[israrkhan]

Hi Ivan,

i think its hard to convert into qlikview, but i think...

1) first you should read data on left join, you will get one table right, without condition value2.Table2 < value1.Table1, mean you will have whole data, in table1.

2) then load the data again in RESIDENT LOAD from (Table1), and apply the condition, you will get a new table.

3) then DROP the previous table(table1).

give a try to it,,,it might help..

[bbi_mba_76 · Accepted solution]

Hi,

following Israrhan answer, something like this:

Table1:

LOAD * INLINE [

    ProdID, value1

    1, 12

    2, 10

    3, 8

    4, 6]

    ;

Table2:

LOAD * INLINE [

    ProdID, value2

    1, 13

    2, 9

    3, 7

    5, 1]

    ;

Left join (Table1)

Load ProdID,

    value2

resident Table2

;

Drop table Table2;

Table:

Load ProdID,

    value1,

    if(value1>value2, value2) as value2,

    1

resident Table1

;

Drop table Table1;

[#5]

Thank you, it works fine