topic Re: Rounding in Where clause - Decimal number issue in App Development

Rounding in Where clause - Decimal number issue

onmysi49 — Sat, 16 Nov 2024 17:56:03 GMT

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-2019-and-later/ta-p/1744935

Thanks a lot for any opinions.

Cheers, Ondrej

Re: Rounding in Where clause - Decimal number issue

Kushal_Chawda — Thu, 22 Oct 2020 11:10:27 GMT

@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)';

Re: Rounding in Where clause - Decimal number issue

onmysi49 — Thu, 22 Oct 2020 12:16:07 GMT

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=',';

Re: Rounding in Where clause - Decimal number issue

Kushal_Chawda — Thu, 22 Oct 2020 13:43:00 GMT

@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)';

Re: Rounding in Where clause - Decimal number issue

onmysi49 — Thu, 22 Oct 2020 14:26:07 GMT

@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.

Re: Rounding in Where clause - Decimal number issue

onmysi49 — Fri, 23 Oct 2020 07:45:06 GMT

@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.

Re: Rounding in Where clause - Decimal number issue

Kushal_Chawda — Fri, 23 Oct 2020 09:59:37 GMT

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