Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Problem with decimal values from SQL

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

1 Solution

Accepted Solutions
MarcoWedel

7 Replies
Chanty4u
MVP
MVP

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
Not applicable
Author

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
Specialist
Specialist

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


RoundExample.PNG

MarcoWedel

maybe helpful:

Rounding Errors

regards

Marco

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

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