topic Intervalmatch with three date fields in QlikView

Intervalmatch with three date fields

Tue, 03 Feb 2015 12:24:09 GMT

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

Re: Intervalmatch with three date fields

Tue, 03 Feb 2015 12:37:25 GMT

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

Re: Intervalmatch with three date fields

Gysbert_Wassenaar — Tue, 03 Feb 2015 12:39:31 GMT

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