topic Downloading from SQL Server 2008 in QlikView

Downloading from SQL Server 2008

Fri, 07 Oct 2011 07:48:50 GMT

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.

Downloading from SQL Server 2008

its_anandrjs — Fri, 07 Oct 2011 07:58:27 GMT

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

kaushiknsolanki — Fri, 07 Oct 2011 08:02:05 GMT

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

Downloading from SQL Server 2008

SunilChauhan — Fri, 07 Oct 2011 08:11:02 GMT

properties->number-> fixed to and type 2

it show you ressult till 2 precision

Downloading from SQL Server 2008

Fri, 07 Oct 2011 08:12:57 GMT

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

kaushiknsolanki — Fri, 07 Oct 2011 08:16:06 GMT

Hi,

ok then try this.

load text(ProdID) as ProdID

from xyz.

Regards,

Kaushik Solanki

Downloading from SQL Server 2008

Fri, 07 Oct 2011 10:14:16 GMT

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

kaushiknsolanki — Fri, 07 Oct 2011 10:21:33 GMT

Hi,

Try this way.

Load text(ProdID) as ProdID,

Field1,

Field2;

SQL select * from xyz;

Regards,

Kaushik Solanki