Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
The new version of ODBC has been installed on the QV server the problem is not occurring anymore.
Problem solved
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
It is not problem with the format when I do sum(u_hours) i have two different results 6 and 7,5
Try like this in qlikview
Num(Sum(u_hours),'##.#0')
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
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
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
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
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
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