Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
Not applicable

Intervalmatch with three date fields

Hi!

Is it possible to create intervalmatch from three date fields? I want to link every date between added date and handshake OR delivery date to the Id. Using handshake date only when delivery date is null. I've tried like this:

join (MasterCalendar) load Distinct Id Resident Attempt205;

inner join IntervalMatch (date, Id) LOAD
[Added Date],
[Delivery Date,
Id
Resident Attempt205
where not IsNull([Delivery Date]);

inner join IntervalMatch (date, Id) load
[Added Date],
[Handshake Date],
Id
Resident Attempt205
where IsNull([Delivery Date])
;

But it is not working. When there is only one of the intervalmatches it is working otherwise fine expect it creates a synthetic key. I would also like to know what is the best way to get rid of the synthetic key?

Thanks for your help!

Reg, Heidi

Tags (1)
2 Replies
Highlighted
Not applicable

Re: Intervalmatch with three date fields

I did an easy solution with combining handshake and delivery date to "end date"-field according to if delivery date exists or not and this way I only need one intervalmatch. But if someone knows what was my mistake in the previous one please share your knowledge (:

And I still have the synthetic key problem if someone can help me out with it! 

-Heidi

Highlighted
MVP & Luminary
MVP & Luminary

Re: Intervalmatch with three date fields

First create a new field in the Attempt205 table:

LOAD

     Id,

     [Added Date],

     [Delivery Date],

     [Handshake Date],

     rangemax([Delivery Date],[Handshake Date]) as IntervalMatchEndDate,

     ...other fields...

FROM ...somewhere...

Then use the new field IntervalMatchEndDate as the second date field in the intervalmatch.

edit: ah, well nevermind. You just found that solution yourself. Well done! The intervalmatch function will result in a synthetic key. This is not a problem. See this blog post: IntervalMatch


talk is cheap, supply exceeds demand