topic Re: INTERVALMATCH works with only some timestamps in QlikView

INTERVALMATCH works with only some timestamps

Polina — Sat, 16 Nov 2024 00:38:12 GMT

Dear Qlik specialists and enthusiasts,

I am having some trouble with INTERVAL MATCH function on Qlikview which I am not able to solve for more than a day already. I am wondering whether any of you have ideas how to solve it or why this problem occurs. I have a table with some times which looks like that:

Date	start time	end time
1/2/2020 12:00:00 AM	10:00	10:30
1/2/2020 12:00:00 AM	10:30	10:45
1/2/2020 12:00:00 AM	10:45	11:00

It also has some other fields, but they are currently not relevant

I am trying to match it with the second table with time periods which looks the same (date, start time, end time and some other fields.)

In sql my script looked like that:

select * from Table 1 as a

left outer join Table 2 as b on

a.datum = b.datum and a.[start time] between b.[start time] and b.[end time]

I am trying to achieve the same results with Intervalmatch in Qlikview

Table 2:
LOAD
*
FROM [..\QV_Datamarts\Table2.qvd]
(qvd)

Table 1:
LOAD *

FROM [..\QV_Datamarts\Table1.qvd]
(qvd)

LEFT JOIN (Table 1)
INTERVALMATCH ([start time], [Date])
LOAD
[start time]
,[end time]
,[Date]
RESIDENT [Table 2]
;
LEFT JOIN ([Table 1])
LOAD *
RESIDENT [Table 2]
;

The problem is that the match sometimes does not work. For example I have start time 13:50:00 in the first table and it does not match with 13:50:00 - 13:59:00 in the second table, while it does work for some other times. Funny enough when I apply time# ([start time], 'hh:mm:ss') on the start time in the first table it finds more matches, but still not all,

Does any of you have some ideas why this can be the case? I tried to reformat the times in a similar way in both tables, but it still does not work. I am going crazy...

Re: INTERVALMATCH works with only some timestamps

sunny_talwar — Wed, 20 May 2020 12:28:58 GMT

I think this has to with Rounding Errors and another one Rounded numerical values sometimes get unexpected results. This happens because time is decimal number (1 day or 24 hour = 1 and 12 hours = 0.50). So, one way to may be address this to slighly decrease your start time (by 0.000000001) and slighly increase your end time (by 0.000000001) for your range and see if that resolve your issue.

Re: INTERVALMATCH works with only some timestamps

Polina — Wed, 20 May 2020 15:54:22 GMT

It looks like you are right. What seemed to help (I am a bit afraid to say that it works in all cases) is to do first Time and then #Time conversion like that Time#(time([start time], 'hh:mm:ss'),'hh:mm:ss'). Some useful information for why comparison between time stamps works/doesn't work is provided here: https://qlikviewcookbook.com/2011/10/correct-time-arithmetic/

I will leave this topic open for now just in case (still not sure whether my script runs as planned).