Rounding in Where clause - Decimal number issue

[onmysi49]

Hello,

I might be missing something very simple but I cannot figure out why this is happening:

I have a table MaxTS. The only purpose of this table is to find Max TimeStamp value from my source:

Max_TS:
Load
    Max(round("TimeStamp",0.00001)) as MaxTS
FROM [$(vSapL1Path)/YFIAPL5_QLIK.qvd]
(qvd);

then I store it into variable call vMaxTSap:

Let vMaxTSap = peek('MaxTS',0);

And then I would like to Load a table from my source which contains only records with this max TimeStamp:

LOAD *    
FROM [$(vSapL1Path)/YFIAPL5_QLIK.qvd]
(qvd)
where round("TimeStamp",0.00001) = $(#vMaxTSap);

The issue is that 0 lines are fetched here because apparently the values in where clause do not equal for any of the records.

The strange thing is that this issue occurs only if numbers are rounded to 5 decimal places. If I change the round function to 0.000001 or 0.0001, it works, numbers are equal and 914 lines are fetched from the source.

When I use Trace to see the vMaxTSap variable it reads: 44125.59211 in the output. The Max TimeStamp in the source is 10/21/2020 2:12:38 PM (stored in thes TS format).

It seems that round function returns a different number in the Where clause than in the Load itself?

I am thinking if it is not related to: https://community.qlik.com/t5/Support-Knowledge-Base/Decimal-Values-In-Qlik-Sense-And-QlikView-June-...

Thanks a lot for any opinions.

Cheers, Ondrej

[Kushal_Chawda]

@onmysi49  try below

Max_TS:
Load
    num(Max("TimeStamp")) as MaxTS
FROM [$(vSapL1Path)/YFIAPL5_QLIK.qvd]
(qvd);

Let vMaxTSap = peek('MaxTS',0);

LOAD *    
FROM [$(vSapL1Path)/YFIAPL5_QLIK.qvd]
(qvd)
where num("TimeStamp") = '$(vMaxTSap)';

[onmysi49]

Hello @Kushal_Chawda,

thanks a lot for such a quick reply.

Unfortunately this does not work either, no lines fetched. Also I have tried several different combinations with num() functions already. And nothing worked. I figured out that only option that seems to work is to round the numbers. But I am puzzled why it works only with certain number of decimal places.

Thanks a lot. Have a good one

EDIT: my default dec. sep is comma - SET DecimalSep=',';

[Kushal_Chawda]

@onmysi49  one more thing you can try to actually avoid rounding issue like below

Max_TS:
Load
    floor(Max("TimeStamp"),1/24/60) as MaxTS
FROM [$(vSapL1Path)/YFIAPL5_QLIK.qvd]
(qvd);

Let vMaxTSap = peek('MaxTS',0);

LOAD *    
FROM [$(vSapL1Path)/YFIAPL5_QLIK.qvd]
(qvd)
where floor("TimeStamp",1/24/60) = '$(vMaxTSap)';

[onmysi49]

@Kushal_Chawda ,

thank you. I get your point - to extract only minutes fraction from the timestamp. This does not seem to work anyway.

What actually works is:

floor("TimeStamp",0.00001)
round("TimeStamp",0.000001)

(there are several more versions with different number of dec. places of floor() and round() which work)

But I am still wondering why it does not work for round() with 5 dec. places. What is the root cause? And if e.g. I use round with 9 dec. places, is it reliable when TS is different in future reloads of the application? I doubt that.

I am sorry for digging into it so much, however, I would like to know what is behind that.

Thanks a lot. Have a good one.

[onmysi49 · Accepted solution]

@Kushal_Chawda ,

after reading https://community.qlik.com/t5/Qlik-Design-Blog/Rounding-Errors/ba-p/1468808

I guess I understand what's going on here. Converting to seconds with round(24*60*60*"TimeStamp") works like a charm.

Do you think my understanding is correct here?

Thanks a lot. Have great day.

[Kushal_Chawda]

@onmysi49  yeah that's the way to rounding till seconds.