Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

SQL LEFT JOIN using between operator

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





Tags (1)
3 Replies
MVP
MVP

SQL LEFT JOIN using between operator

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]
;

ester_pr
New Contributor

SQL LEFT JOIN using between operator

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

leni_balakrishn
Contributor III

SQL LEFT JOIN using between operator

Try to replace the keyword "Between" as below

join ...

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

Community Browser