topic Re: Compare times in QlikView

Compare times

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

hi all,

trying to compare two times and seem to get wrong results

expression: if(time(FinishTime) < time(SLA),1,0)

time format used HH:MM:SS

example: finish time is 5:10:59

SLA: 7:00:00

here I should get 1 but getting 0

Anyone knows why?

Re: Compare times

sunny_talwar — Thu, 15 Dec 2016 20:31:58 GMT

If it is truly read as times by QlikView, you might not even need Time() function.

If(FinishTime < SLA, 1, 0) as Flag

But it seems they are not read as time, so try this:

If(Time#(FinishTime, 'h:mm:ss') < Time#(SLA, 'h:mm:ss'), 1, 0) as Flag

Re: Compare times

MarcoWedel — Thu, 15 Dec 2016 22:48:30 GMT

I guess your FinishTime and SLA fields are timestamps, i.e. they have an integer date part you have to get rid of to just compare the fractional time part of the timestamp. (The Time() function you used doesn't change the numerical value of your timestamp, it only formats it.)

So one solution might be:

If(Frac(FinishTime) <  Frac(SLA),1,0)

or shorter

-(Frac(FinishTime) <  Frac(SLA))

as boolean true has a value of -1 while false is represented by 0.

hope this helps

regards

Marco