Times

[John-SSI]

Hi, what do i miss here??

if(frac(STARTTIME)>=MakeTime(6,45) and frac(STARTTIME)<=MakeTime(15,29,59),'FM',
if(frac(STARTTIME)>=MakeTime(15,30) and frac(STARTTIME)<=MakeTime(22,59,59),'EM','Natt')) as Shift

[BrunPierre]

Hi, perhaps

If(Frac(STARTTIME) >= MakeTime(6,45) and Frac(STARTTIME) <= MakeTime(15,29,59), 'FM',
If(Frac(STARTTIME) >= MakeTime(15,30) and Frac(STARTTIME) <= MakeTime(22,59,59), 'EM',
'Natt')) as Shift

[John-SSI]

Hi, got the same result!

[marcus_sommer]

Time values are as a part of 1 no integer. Because of the fact that Qlik doesn't used a decimal number-system else a binary ones it's not possible to produce exact values. Therefore a comparing against = or <= >= may result in a wrong matching.

A workaround for such scenarios would be to round both sides of the comparing identically, maybe like:

floor(Frac(STARTTIME), 1/24/60/60) >= floor(MakeTime(6,45), 1/24/60/60)

[John-SSI]

Interesting, but i do something wrong here?
(I just tested the FM)
If(Floor(Frac(STARTTIME),1/24/60/60) >= Floor(MakeTime(6,45) and Floor(Frac(STARTTIME),1/24/60/60) <= Floor(MakeTime(15,29,59), 1/24/60/60)), 'FM',
If(Frac(STARTTIME) >= MakeTime(15,30) and Frac(STARTTIME) <= MakeTime(22,59,59), 'EM',
'Natt')) as Shift

[marcus_sommer]

Yes it's not quite correct because the floor(VALUE, 1/24/60/60) needs to be exactly applied to each part of each comparing. Especially skipping it here: Floor(MakeTime(6,45)) means that's not rounded to a second else to an integer and here ZERO.

Before applying such comparing in an if-loop it would be useful to return all essential parts within own fields / expressions, like

num(MakeTime(6,45), '#.00000000000000')
num(floor(MakeTime(6,45), 1/24/60/60), '#.00000000000000')
time(Frac(STARTTIME))
num(floor(frac(STARTTIME), 1/24/60/60), '#.00000000000000')
...

to see all results directly which is also mostly a direct hint why a condition-expression returned an unexpected result.

[John-SSI]

Hi all, i fixed this. Reading database values and ETL --> QVD. So the time of and timestamp get's corrected 🙂
Thanx all for your help.