topic Left join with where clause in load in QlikView

Left join with where clause in load

Fri, 12 Aug 2011 17:27:27 GMT

TabA

a, b

1, '2011-01-01'

2, '2011-02-01'

2, '2011-03-01

TabB

c, d, e

1, '2011-01-01', '2011-01-31'

2, '2011-01-01', '2011-03-31'

Sample in the sql command:

select *

from taba

left join tabb on a = c and b >= d and b <= e

How can i do this in the load?

Left join with where clause in load

SunilChauhan — Fri, 12 Aug 2011 17:42:05 GMT

TabA:

select a,b

from taba

TabB:

select c as a,

from tabB

TabC:

Load

a,d,e,c

resident TabB

left join

Load

a,b

resident TabA;

TabD:

Load

a,b,d,e

resident TabC

where a = c and b >= d and b <= e;

drop tablesTabB,TabA,TabC ;

Left join with where clause in load

Fri, 12 Aug 2011 18:11:58 GMT

I tried to load this script but not functioned.

TabA:

LOAD * INLINE [

a, b

1, '2011-01-01'

2, '2011-02-01'

2, '2011-03-01'

];

TabB:

LOAD c as a, c, d, e INLINE [

c, d, e

1, '2011-01-01', '2011-01-31'

2, '2011-02-01', '2011-01-01'

];

TabC:

Load

a,d,e,c

resident TabB;

left join Load

a,b

resident TabA;

TabD:

Load

a,b,d,e

resident TabC

where a = c

and b >= d

and b <= e;

drop tables TabB,TabA,TabC ;

Re: Left join with where clause in load

johnw — Fri, 12 Aug 2011 20:43:54 GMT

I think this?

TabA:
LOAD * INLINE [
a, b
1, '2011-01-01'
2, '2011-02-01'
2, '2011-03-01'
];
LEFT JOIN (TabA)
LOAD c as a, c, d, e INLINE [
c, d, e
1, '2011-01-01', '2011-01-31'
2, '2011-02-01', '2011-01-01'
];
INNER JOIN (TabA)
LOAD *
RESIDENT TabA
WHERE b >= d AND b <= e;

Left join with where clause in load

Tue, 16 Aug 2011 12:27:19 GMT

Hi John,

I need something a little different.

TabA:

LOAD * INLINE [

a, b

1, '2011-01-01'

2, '2011-02-01'

2, '2011-03-01'

2, '2011-04-01'

];

LEFT JOIN (TabA)

LOAD c as a, c, d, e INLINE [

c, d, e

1, '2011-01-01', '2011-01-31'

2, '2011-02-01', '2011-03-31'

];

LEFT JOIN (TabA)

LOAD *

RESIDENT TabA

WHERE b >= d AND b <= e;

I need the result below:

1, '2011-01-01', '2011-01-01', '2011-01-31'

2, '2011-02-01', '2011-02-01', '2011-03-31'

2, '2011-03-01', '2011-02-01', '2011-03-31'

2, '2011-04-01', null, null

Thanks

Re: Left join with where clause in load

lironbaram — Tue, 16 Aug 2011 12:36:22 GMT

hei

try this one

TabA:

LOAD * INLINE [

a, b

1, 01/01/2011

2,01/02/2011

2, 01/03/2011

2, 01/04/2011

];

LEFT JOIN (TabA)

LOAD c as a, c, d, e INLINE [

c, d, e

1, 01/01/2011, 31/01/2011

2, 01/02/2011,31/03/2011

];

LEFT JOIN (TabA)
LOAD b,
d as F,
e as G
RESIDENT TabA
WHERE b >= d AND b <= e;

drop fields d,e;

RENAME Field F to d;
RENAME Field G to e;

Re: Left join with where clause in load

Tue, 16 Aug 2011 12:54:56 GMT

Thanks guys!