topic Re: Problem with decimal values from SQL in QlikView

Problem with decimal values from SQL

Anonymous — Mon, 19 Mar 2018 13:50:22 GMT

Hello,

we have a current Problem with decimal values in QlikView 12.1 SR8, which we get from MS SQL Server.

The data source is a View in MS SQL Server. Format is decimal(18,2).

SQL Output - 10122.84

QlikView Input Default - 10122.84 (Default field)

If I set a numeric Options of the field in QlikView to decimal 14, QlikView Shows - 10.122,84000000000015.

We can Format the Display of the data, but QlikView calculates in background with wrong values.

I made some Tests with any Formats in SQL and QlikView but I cannot find a solution.

Curios, this Problem has not really all numeric values. I would say, 20 percent (see attachment).

Thanks for your help.

Dimitri

Re: Problem with decimal values from SQL

Chanty4u — Mon, 19 Mar 2018 15:49:53 GMT

try here

==Round(Field, 0.01)

How do you round a number to two decimal places C# VB.Net Asp.Net

Re: Problem with decimal values from SQL

Digvijay_Singh — Mon, 19 Mar 2018 16:06:56 GMT

This statement has a problem in syntax-

Num#(V_BETRAG_TEXT,' #,##0.00','.')/100 as V_BETRAG_TEXT

I think it should be like -

Num#(V_BETRAG_TEXT,' #,##0.00',',','.')/100 as V_BETRAG_TEXT

Your dec separater is ',' and thousand separator is '.', right?

Re: Problem with decimal values from SQL

Anonymous — Mon, 19 Mar 2018 16:18:35 GMT

No, dec separater is '.'. You can see it in Word attachment, point 3, column V_BETRAG_TEXT.

With ==Round it doesn't work too. It is the Point 4: round(V_BETRAG,0.01) as V_BETRAG_ROUND

If I set Option numeric of field V_BETRAG_ROUND to decimal 14, I have the same Problem.

Re: Problem with decimal values from SQL

wdchristensen — Tue, 20 Mar 2018 20:34:55 GMT

I think you should try to round / truncate the values on the SQL Server query. So you could try something like the code below and see if it cleans your data on the load side. Please let me know if this was helpful. Good luck!

Select *, CONVERT(money,ROUND(@exampleNumber, 2, 1)) as V_BETRAG_MONEY_TEST

From someView

Re: Problem with decimal values from SQL

MarcoWedel — Tue, 20 Mar 2018 23:51:51 GMT

maybe helpful:

Rounding Errors

regards

Marco

Re: Problem with decimal values from SQL

Anonymous — Wed, 21 Mar 2018 08:15:38 GMT

Hello William, I have not to round the SQL values because SQL datatype already is decimal(18,2).

Re: Problem with decimal values from SQL

Anonymous — Wed, 21 Mar 2018 08:41:12 GMT

Thanks, this is a really good Explanation of our Problem. The solution we have to find ourselves.