Problem with decimal values from SQL

[Anonymous]

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

[Chanty4u]

try here

==Round(Field, 0.01)

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

[Digvijay_Singh]

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?

[Anonymous]

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.

[wdchristensen]

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

[MarcoWedel · Accepted solution]

maybe helpful:

Rounding Errors

regards

Marco

[Anonymous]

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

[Anonymous]

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