Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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]
;
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
Try to replace the keyword "Between" as below
join ...
ON (Date >= DateStart AND Date <= DateEnd) and ...