IntevalMatch on values that cross midnight

andrewmo — Fri, 10 Mar 2017 15:50:44 GMT

Hello

I have a load statement that is doing an interval match by minute of day. It appears to be working only for cases that occur on the same date. Here is an example of an instance where a case crosses midnight and not getting any values for intervalmatch:

CASES:

LOAD * INLINE

[

LOG,SurgDt,InTime,OutTime

240819,03/06/17,18:40:00,00:21:00

];

MINODAY:

Load interval(frac(recno()/1440),'h:mm') as MinOfDay autogenerate(1440);

LOGTIME:

LOAD DISTINCT LOG,

InTime,

OutTime,

interval(InTime+(IterNo()-1)/1440, 'h:mm') as MinOfDay

RESIDENT CASES

While InTime+(IterNo()-1)/1440<=OutTime;

NoConcatenate

MINS:

Load MinOfDay,LOG Resident LOGTIME;

INNER JOIN (MINS)

IntervalMatch(MinOfDay,LOG)

LOAD InTime, OutTime

Resident LOGTIME;

DROP TABLE LOGTIME;

Any suggestions would be greatly appreciated.

Thanks

Re: IntevalMatch on values that cross midnight

marcus_sommer — Fri, 10 Mar 2017 18:07:40 GMT

I think if there is a day-shift included you will need to match your data on a timestamp and not only on time and you might need to adjust your source with something like this:

CASES:

LOAD

LOG,SurgDt,

date(SurgDt+InTime) as InTime,

date(SurgDt+OutTime+if(OutTime<InTime,1,0)) as OutTime

INLINE [

LOG,SurgDt,InTime,OutTime

240819,03/06/17,18:40:00,00:21:00];

- Marcus

topic IntevalMatch on values that cross midnight in QlikView

IntevalMatch on values that cross midnight

Re: IntevalMatch on values that cross midnight