topic Intervalmatch between two time functions or Timestamps in QlikView

Intervalmatch between two time functions or Timestamps

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have the below dates and need to define shifts . We have a dayshift and a nightshift.

Timestamp format for shifts:

dd.mm.yyyyhh:mm:ss

What I need to do is create a field that is called Shiftdate.

Shiftdate will be calculated by checking whether the hour of the timestamp > 00:00:00 (12 at night) and < 06:00:00 (6 in the morning)

and would then need to -1 from the timestamp (Day -1).

Meaning that all early morning work still forms part of the previous day's shift.

The reason I need to do this, is that past 12 at night the date changes to the next day, however we still see it as the same shiftdate as before 12 at night.

Is there a way I could use Interval match to resolve this issue?

Thanks in advance

Re: Intervalmatch between two time functions or Timestamps

stabben23 — Fri, 17 Mar 2017 09:44:25 GMT

Hi,

try With something like this.

if(time(frac(timestamp(CreationDate,'hh:mm:ss')))>=time('06:00:00'),timestamp(CreationDate),timestamp(timestamp(CreationDate)-1)) as %DateKey,

Re: Intervalmatch between two time functions or Timestamps

Anonymous — Fri, 17 Mar 2017 09:46:59 GMT

Hi Staffan

My solution is the below. however , i was just curious to see if interval match could be used.

IF(HOUR(CONFIRMED_AT)<'06' AND HOUR(CONFIRMED_AT)>'00',TRIM(DATE(INTERVAL(DATE(CONFIRMED_AT) - 1,'DD'),'M/D/YYYY')),TRIM(DATE(CONFIRMED_AT))) AS SHIFTDATE,

Re: Intervalmatch between two time functions or Timestamps

stabben23 — Fri, 17 Mar 2017 09:53:54 GMT

ok Ruan,

I have never used intervallmatch in a case like this. Feels like a "overkill" solution to fix the problem.

I use my solution to step back one day when the skift is working to less then 06:00:00