topic Re: Decimal issue while fetching data into QVD file in QlikView

Decimal issue while fetching data into QVD file

waszcma1 — Tue, 31 Jan 2017 22:51:07 GMT

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.5

2.25

0.25

but the same script used on QV server returns this:

u_hours

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

Re: Decimal issue while fetching data into QVD file

rwunderlich — Wed, 01 Feb 2017 02:37:16 GMT

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

Re: Decimal issue while fetching data into QVD file

waszcma1 — Wed, 01 Feb 2017 06:54:43 GMT

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

Re: Decimal issue while fetching data into QVD file

Anil_Babu_Samineni — Wed, 01 Feb 2017 07:04:52 GMT

Try like this in qlikview

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

Re: Decimal issue while fetching data into QVD file

waszcma1 — Wed, 01 Feb 2017 08:39:40 GMT

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

Re: Decimal issue while fetching data into QVD file

Anil_Babu_Samineni — Wed, 01 Feb 2017 08:43:53 GMT

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

Re: Decimal issue while fetching data into QVD file

waszcma1 — Wed, 01 Feb 2017 11:08:46 GMT

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

Re: Decimal issue while fetching data into QVD file

Anil_Babu_Samineni — Wed, 01 Feb 2017 11:13:24 GMT

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

Re: Decimal issue while fetching data into QVD file

waszcma1 — Wed, 01 Feb 2017 11:33:49 GMT

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

Re: Decimal issue while fetching data into QVD file

marcus_sommer — Wed, 01 Feb 2017 12:33:09 GMT

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

Re: Decimal issue while fetching data into QVD file

swuehl — Wed, 01 Feb 2017 12:37:24 GMT

Marek Waszczak wrote:

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 don't fully understood this approach.

I would suggest to read in numbers as numbers and handle formatting on the QV side, i.e. remove the CAST() from your DB SELECT.

Re: Decimal issue while fetching data into QVD file

waszcma1 — Sun, 05 Feb 2017 22:04:19 GMT

I have load and joined the data from 2 qvds one was generated on laptop and one from the server

there is no doubt that on the server all numbers after dot are missing while is loaded via ODBC.

Re: Decimal issue while fetching data into QVD file

waszcma1 — Wed, 15 Feb 2017 12:05:39 GMT

The new version of ODBC has been installed on the QV server the problem is not occurring anymore.

Problem solved