topic Match time interval? in QlikView

Match time interval?

Wed, 25 Nov 2020 16:16:04 GMT

Hi guys,

I have this situation and i don't know exactly which formula should i use, I have tried to use "IntervalMatch" but with no success.

For the below i want to identify only the "OrderId" where "Date_WS" is between the interval "From" and "To". For the below example the id 2178515 should not appear since "Date_ws" is not between the mentioned interval. I have to take in consideration the hour interval as well.

OrderId	From	To	Date_WS
1964716	2017-01-03 01:00:00.0000	2017-02-02 23:00:00.0000	2017-02-02 10:04:44.444444
1984148	2017-01-03 01:00:00.0000	2017-02-02 23:00:00.0000	2017-01-05 13:16:18.16181618
2178515	2017-01-03 01:00:00.0000	2017-02-02 23:00:00.0000	2017-02-01 14:12:57.12571257
2178515	2017-01-03 01:00:00.0000	2017-01-03 23:00:00.0000	2016-11-22 10:41:51.41514151

Re: Match time interval?

marcus_malinow — Wed, 12 Apr 2017 09:02:47 GMT

Hi Vlad,

as you have all the values within the same table, IntervalMatch might be overkill for this.

If you do this in your load script, you can use the formula

if (From <= Date_WS and Date_WS <= To, 1, 0) - to generate a flag

or just use From <= Date_WS and Date_WS <= To in a WHERE clause to limit your data load

Marcus

Re: Match time interval?

ahaahaaha — Wed, 12 Apr 2017 09:03:52 GMT

Hi Vlad,

If in a script, then as an variant

LOAD*Inline

[OrderId, From, To, Date_WS

1964716, 2017-01-03 01:00:00.0000, 2017-02-02 23:00:00.0000, 2017-02-02 10:04:44.444444

1984148, 2017-01-03 01:00:00.0000, 2017-02-02 23:00:00.0000, 2017-01-05 13:16:18.16181618

2178515, 2017-01-03 01:00:00.0000, 2017-02-02 23:00:00.00000, 2017-02-01 14:12:57.12571257

2178515, 2017-01-03 01:00:00.0000, 2017-02-02 23:00:00.0000, 2016-11-22 10:41:51.41514151]

Where Date_WS>=From And Date_WS<=To;

Result

Regards,

Andrey