topic Re: Qlikview : Conditional join QVD in QlikView

Qlikview : Conditional join QVD

Wed, 26 Jun 2013 06:10:38 GMT

Hi,

Please give me some input on these :

I have a table 1 (fields : start_time) and table 2 (fields : start_time, end_time)

i wanted to join them like this :

SQL statement :

select *

from table1 a

join table2 b

on a.start_time<= b.start_time AND b.start_time <= a.end_time

qlikview statement :

Thanks,

Re: Qlikview : Conditional join QVD

er_mohit — Wed, 26 Jun 2013 06:30:42 GMT

qualify *;

load start_time,

sql select * from a;

join

qualify *;

load start_time,

end_time,

SQL Select *

from b;

new:

load * from a

where a.start_time<= b.start_time AND b.start_time <= a.end_time;

hope it helps

Re: Qlikview : Conditional join QVD

Wed, 26 Jun 2013 06:40:04 GMT

load

rowno() as key,

start_time as start_time_a,

end_time as end_time_a;

sql select * from table a;

join(a)

load

rowno() as key

start_time as start_time_b

end_time as end_time_b

sql select * from table b;

load *

resident a

where start_time_a <= start_time_b and start_time_b <= end_time_a

Re: Qlikview : Conditional join QVD

daveamz — Wed, 26 Jun 2013 06:59:50 GMT

Hi,

You can use intervalmatch. See: http://community.qlik.com/blogs/qlikviewdesignblog/2013/04/04/intervalmatch

Regards,

David

Re: Qlikview : Conditional join QVD

Wed, 26 Jun 2013 08:48:10 GMT

try the below one...

table1:

LOAD a_starttime,a_endtime,'a' as dummy;
SQL SELECT * FROM table1
join table

table2

LOAD b_starttime,b_endtime,'a' as dummy;
SQL SELECT * FROM table2

table3:

load *
resident table1 where a_starttime<= b_starttime AND b_starttime <= a_endtime

drop tables table1