Decimal issue while fetching data into QVD file

[waszcma1]

Hi,

I have noticed some issue on the QV server.

I am fetching data from mySQL database via ODBC connection to the QVD file using the following scritp

SET ThousandSep='.';

SET DecimalSep=',';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='£#,##0.00;-£#,##0.00';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

ODBC CONNECT TO ConnectionName (XUserId is ************************, XPassword is ***********************);

//example of 1 day data for 1 user

let vTableName ='day_record'; //define the table name

QUALIFY *;

$(vTableName):

LOAD

  "dv_u_category",

    "dv_u_task",

    "u_task",

    "dv_u_user",

    "sys_id",

    "u_date",

    "u_hours",

    "u_time_card",

    "u_user";

SQL SELECT "dv_u_category",

    "dv_u_task",

    "dv_u_user",

    "u_task",

    "sys_id",

    "u_date",

    "u_hours",

    "u_time_card",

    "u_user"

FROM "SCHEMA".OAUSER."$(vTableName)" WHERE sys_updated_on BETWEEN '2016-12-01 00:00:00' AND '2016-12-01 23:59:59' AND  dv_u_user LIKE 'USER001';

UNQUALIFY *;

The problem I cant solve is that If I run the script from my laptop, data in the u_hours looks like that:

u_hours

0

0

0.5

2.25

0.25

0

4

0

0.25

0.25

0

0

but the same script used on QV server returns this:

u_hours

0

0

0

2

0

0

4

0

0

0

0

0

De ODBC has been configured ages ago both on the server and my laptop at the same time (never changed any configuration or local settings)

What can be the root cause of this issue ?

I have tryied swiched comma and dot in the file on the QV server

SET ThousandSep='.';

SET DecimalSep=',';

it didn't help

[rwunderlich]

The two QVWs likely have a different default format for u_hours. However, the underlying value is the same, it's just the display value.

Open the Document Properties, Number tab in each document and look at the format setting for u_hours. Also check the Number Format in the listbox you are using.

-Rob

[waszcma1]

It is not problem with the format when I do sum(u_hours) i have two different results 6 and 7,5

[Anil_Babu_Samineni]

Try like this in qlikview

Num(Sum(u_hours),'##.#0')

[waszcma1]

well this is not that problem

I have tested each line usign

if(u_hours=0,1,0) and all records wher should be 0.5 or 0.25 are flagged as 1

[Anil_Babu_Samineni]

Use This expression by creating of your Flag

Num(Sum({<Flag_Name = {1}>}u_hours),'##.#0')

Note: I would highly recommend you to create one dummy data and then please share your application to work on it. So that, we can help you at a shot

[waszcma1]

I am sure this is not a problem with display

I have run script to generate QVD file

This is used on the QV server

The same app I have lunched from my laptop and changed the name of the QVD

Now I have opened the Interactive sql console from my laptop

OK now let see what contains both QVDs files one generated on my lapotp and one from serwer

from my laptop:

QVD generated on the QV server but opened localy on my laptop

I have converted number data to the char while fetching into server

As you can see we lost all numbers after comma

The problem might be version of ODBC driver (server has 7.01.0095, my laptop 8.00.0017) but I am not sure if this is the right direction. It is not so easy for me to force update the driver on the server so I need to be sure about that what is the root cause of this issue

[Anil_Babu_Samineni]

I have converted number data to the char while fetching into server

Why you want to change Number to Char here. Keep as number and then see in server. Because, Qlik won't change any format while loading the tables. If we change that could be the effect

[waszcma1]

I did it for purpose

Converting numeric value to the char we have no doubt how to display this value and shows it as a text so there is no way that the numbers after coma can gone.

I used this conversion to show you that when I run the same script on both machines (latpop, server) I have totaly different output and there is clearly while the ODBC is fetching data, I have also used QEye to check the content of both QVDs file and there is no doubt that QVD generated on the server has missing decimal value after comma

Both QVD were generated using the same script the same connection but from 2 different machines

QVD generated from the laptop

QVD generated on the QV server

[marcus_sommer]

I don't think that the storing into a qvd removed the decimals else that those data aren't loaded correctly into qlikview.

At first I would check the data directly within a tablebox after loading into qlikview. Then I would use text(u_hours) as u_hours within the preceeding part. Aften them I would force these field to be alpha-numeric value within the SQL-part with something like this:

...

"a - " & "u_hours" & " - x" as "u_hours"

...

If then the "u_hours" part from the string is without decimals you should really look to the odbc-driver and update them or maybe there are also another odbc/oledb-driver available.

- Marcus