topic SQL LEFT JOIN using between operator in QlikView

SQL LEFT JOIN using between operator

Wed, 24 Mar 2010 22:15:01 GMT

Hi,

I tried to mimic the following SQL statement in QlikView, but without success. Can anyone help to inspire something for me?

Select .....

From....

Left

join ...

ON (Date BETWEEN DateStart AND DateEnd) and ...

PTT

SQL LEFT JOIN using between operator

johnw — Wed, 24 Mar 2010 22:34:20 GMT

I think you're looking for an INTERVALMATCH. Here's an example joining on a date range and on two additional fields.

[Contracts]:
LOAD * INLINE [
Contract, Client, Item, Start, End, Price
1, Client A, Item A, 01/01/08, 12/31/08, 50
2, Client A, Item A, 01/01/09, 12/31/09, 60
3, Client A, Item B, 01/01/08, 06/30/09, 70
4, Client A, Item B, 07/01/09, 12/31/09, 75
5, Client B, Item A, 01/01/08, 06/30/08, 40
6, Client B, Item A, 07/01/08, 12/31/09, 50
];

[Sales]:
LOAD * INLINE [
Sale, Client, Item, Date
1, Client A, Item A, 02/15/08
2, Client A, Item A, 04/15/09
3, Client A, Item B, 03/20/09
4, Client A, Item B, 11/30/09
5, Client B, Item A, 02/15/08
6, Client B, Item A, 07/31/08
];

LEFT JOIN ([Sales])
INTERVALMATCH (Date, Client, Item)
LOAD
Start
,End
,Client
,Item
RESIDENT [Contracts]
;
LEFT JOIN ([Sales])
LOAD *
RESIDENT [Contracts]
;
DROP TABLE [Contracts]
;

SQL LEFT JOIN using between operator

ester_pr — Thu, 28 Jul 2011 09:59:49 GMT

Hi,

I have one date coulom and i want to create end date coulom from the next update date,

the table have groups in it

Do you now how to do that?

Thanks Ester

SQL LEFT JOIN using between operator

Anonymous — Thu, 28 Jul 2011 10:41:22 GMT

Try to replace the keyword "Between" as below

join ...

ON (Date >= DateStart AND Date <= DateEnd) and ...