topic Direct Query : Issue with decimal values in Connectivity & Data Prep

Direct Query : Issue with decimal values

Antoine04 — Wed, 08 Nov 2023 13:46:04 GMT

Hello guys,

I am having an issue while using Direct Query and my DATA stored in Snowflake.

The case is as simple as this :

I have a field "TOTAL" and I want to calcule : Sum(TOTAL).

It does not work because it seems DIrect Query does not like decimal values.

When I am calculating Sum(TOTAL_ARRONDI) where TOTAL_ARRONDI is TOTAL rounded, It works.

Any idea about how to solve this ?

Thanks !

Re: Direct Query : Issue with decimal values

marcus_sommer — Thu, 09 Nov 2023 15:20:59 GMT

You may try to play with the number-format and replacing the comma with a dot.

Re: Direct Query : Issue with decimal values

Antoine04 — Wed, 15 Nov 2023 08:16:44 GMT

I tried already, but it did not work neither.

I just think it's not possible to sum decimal values with direct query. Quite annoying 😅

Re: Direct Query : Issue with decimal values

marcus_sommer — Wed, 15 Nov 2023 09:09:35 GMT

Never worked with direct query but I doubt that there is a general issue with decimal values. I think it's rather related to the applied settings and/or the way the data are processed. For example if your default number-format has a comma as decimal delimiter and you assigned a timestamp to variable like:

let v = num(peek('timestamp', -1, 'table'));

your variable will contain a valid value with a comma. Just displaying v in a text-box or similar will work without an error but if you want to calculate with it like:

let x = $(v) + 1;

it will fail. A similar behaviour may occur by various points within the processing.

Beside this you may look for workarounds like multiplying the values at first with something like 1000 and making an integer from the float without loosing information and later it's divided again. Also thinkable would be approaches like fetching the values with floor() and frac() and their lengths and also matching them lately.

Re: Direct Query : Issue with decimal values

Antoine04 — Thu, 23 Nov 2023 09:56:36 GMT

Thank you for your help Marcus !

Regards