Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
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:
SELECT
PROJID
FROM "Kom50_Test".dbo.PROJTABLE
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
SELECT
PROJID
FROM "Kom50_Test".dbo.PROJTABLE
WHERE PROJID = '00001.00'
the same value is displaying as '00001.00'.
How can I get correct value in the result of first statement?
Thanking you.
Hi,
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.
Rgds
Anand
HI,
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.
Load
num(ProjID,'#,##0.00') as ProjID,
.......
;
SQL Select * from XYZ;
Hope its clear to you now.
Regards,
Kaushik Solanki
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.
Hi,
ok then try this.
load text(ProdID) as ProdID
from xyz.
Regards,
Kaushik Solanki
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.
Hi,
Try this way.
Load text(ProdID) as ProdID,
Field1,
Field2;
SQL select * from xyz;
Regards,
Kaushik Solanki