topic Between Two Timestamps in QlikView

Between Two Timestamps

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

Hi All,

Please see below example of code that does not seem to be working correctly.

IF(Time([TIMESTAMP])>=('02:30:01') and Time([TIMESTAMP])<=('09:45:00'),'Night','Day') as SHIFT

Please assist in correcting this, for some reason all values show Day even if it conforms to the condition for Night.

Regards,

Re: Between Two Timestamps

Anonymous — Tue, 19 Jul 2016 04:55:00 GMT

The timestamp is in the following format YYYY/MM/DD hh:mm:ss

Re: Between Two Timestamps

sunny_talwar — Tue, 19 Jul 2016 10:19:52 GMT

May be try this:

If(frac([TIMESTAMP]) >= MakeTime(2, 30, 1) and frac([TIMESTAMP]) <= MakeTime(9, 45, 0), 'Night', 'Day') as SHIFT

Re: Between Two Timestamps

sunny_talwar — Tue, 19 Jul 2016 10:21:12 GMT

Or this, if TIMESTAMP is not read as timestamp:

If(frac(TimeStamp#([TIMESTAMP], 'YYYY/MM/DD hh:mm:ss')) >= MakeTime(2, 30, 1) and frac(TimeStamp#([TIMESTAMP], 'YYYY/MM/DD hh:mm:ss'))<= MakeTime(9, 45, 0), 'Night', 'Day') as SHIFT

Re: Between Two Timestamps

vcanale — Tue, 19 Jul 2016 11:03:40 GMT

Hi,
try specifying the Time Format in your condition:
IF(Time([TIMESTAMP], 'hh:mm:ss' )>=('02:30:01') and Time([TIMESTAMP], 'hh:mm:ss')<=('09:45:00'),'Night','Day') as SHIFT