topic Re: How to apply condition to Left Join? in QlikView

How to apply condition to Left Join?

Sun, 27 Oct 2013 05:47:43 GMT

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?

Re: How to apply condition to Left Join?

preminqlik — Sun, 27 Oct 2013 05:54:40 GMT

use applymap

Re: How to apply condition to Left Join?

israrkhan — Sun, 27 Oct 2013 07:23:59 GMT

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..

Re: How to apply condition to Left Join?

bbi_mba_76 — Sun, 27 Oct 2013 08:01:18 GMT

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,

resident Table1

;

Drop table Table1;

Re: How to apply condition to Left Join?

Mon, 28 Oct 2013 15:15:10 GMT

Thank you, it works fine