Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Downloading from SQL Server 2008

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.

7 Replies

Downloading from SQL Server 2008

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

Downloading from SQL Server 2008

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

  

chauhans85
Esteemed Contributor

Downloading from SQL Server 2008

properties->number-> fixed to and type 2

it show you ressult till 2 precision

Not applicable

Downloading from SQL Server 2008

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.

Downloading from SQL Server 2008

Hi,

   ok then try this.

   load text(ProdID) as ProdID

   from xyz.

Regards,

Kaushik Solanki

Not applicable

Downloading from SQL Server 2008

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.

Downloading from SQL Server 2008

Hi,

     Try this way.

     Load text(ProdID) as ProdID,

     Field1,

     Field2;

     SQL select * from xyz;

Regards,

Kaushik Solanki

Community Browser