Formatting time

[johnan]

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?

[kaushiknsolanki · Accepted solution]

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

[jonathandienst]

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

[johnan]

Thanx, this works perfect!