topic Re: Formatting time in QlikView

Formatting time

johnan — Fri, 28 Feb 2020 10:48:42 GMT

Hi,

Got this:
1.

=if(Interval(EndTime,'hh:mm:ss')-Interval(StartTime,'hh:mm:ss')>='00:00:20',[Pick plan number])
This will show the Pick plan number if the time between end and start is over 20 seconds.
So it works great for me.

Is time from AS400 and formatted in Script:
time(time#(num(PKVSTR,'000000'),'hhmmss')) as StartTime,
time(time#(num(PKLEND,'000000'),'hhmmss')) as EndTime,

----------------------------------------------------------------------------------------------------------------------------

2.
I want to do the same but use the QlikView reload and use the time instead.

=if(Interval(reload_time,'hh:mm:ss')-Interval(StartTime,'hh:mm:ss')>='00:00:04',[Pick plan number])
So the starttime is not changed from above, only reload_time is changed to QlikView's reload time.
This wont work as number 1 above 😞

In script:
time(time#(num(PKVSTR,'000000'),'hhmmss')) as StartTime,
Time(ReloadTime()) AS reload_time

What do i missing?

Re: Formatting time

kaushiknsolanki — Fri, 28 Feb 2020 10:51:33 GMT

Hi, It is because the ReloadTime() gives date as well.

So you will have to remove the date from the calculation and then it will work.

Try this code.

Load

Time(ReloadTime() - Floor(ReloadTime())) as reload_time

Re: Formatting time

jonathandienst — Fri, 28 Feb 2020 11:33:35 GMT

The formatting functions (like Interval() and Time()) do not change the underlying values, so Time(timestamp) does not remove the date. Also formatting functions are just clutter inside a calculation, as you will not see the format that was applied and it has no effect on the result either.

So what i think you need is the load script:

Time(Time#(Num(PKVSTR, '000000'), 'hhmmss')) as StartTime, Time(Frac(ReloadTime())) AS reload_time,

And the expression:

=If(reload_time - StartTime >= 4/86400, [Pick plan number])

or, if you prefer:

=If(reload_time - StartTime >= Interval#(4, 's'), [Pick plan number])

PS: 86400 is the number of seconds in a day

Re: Formatting time

johnan — Fri, 28 Feb 2020 12:41:40 GMT

Thanx, this works perfect!