Discussion Board for collaboration on QlikView Scripting.
I'm new in QV and hope you can help me with the following issue.
I'm connecting to SQL Server using OLE DB Provider.
This is my SELECT Statement:
The value in one of rows is '00001.00'. But it is displaying as '00001' (Zeroes after dot are trimmed).
If I'm using WHERE clause
WHERE PROJID = '00001.00'
the same value is displaying as '00001.00'.
How can I get correct value in the result of first statement?
When you get this PROJID field in the QV application then set is Number format in Properties -> select Number -> and precision is 2 or set in place of number fixed to upto 2 decimal place.
Let me explain what actually happen with both of your code.
In first code qlikview took the ProjID field as number field and by default the number format is set to 0 decimal thus the decimals are trimmed.
In second case you said where ProjID = '00001.00' , as you have defined the 00001.00 in a single quote, qlikview considered this field as a string field and thus it shown you the field as it is.
Now if you want to have number with 2 decimal, you can get it this way.
num(ProjID,'#,##0.00') as ProjID,
SQL Select * from XYZ;
Hope its clear to you now.
properties->number-> fixed to and type 2
it show you ressult till 2 precision
I'm sorry, I forgot to specify, that PROJID is a text field.
The other values, such as '00001.01', '00001.02' etc., are displayed correctly.
ok then try this.
load text(ProdID) as ProdID
Function text() doesn't work in SELECT statement. Standard SQL functions CAST and CONVERT also don't solve the problem.
Really, QV treats text as a numeric value.
If I'm selecting
'P'+PROJID as PROJID
values are correctly dispayed.
It's acceptable solution, but the problem still exists.
Try this way.
Load text(ProdID) as ProdID,
SQL select * from xyz;