topic Leading zeros in string fields in QlikView

Leading zeros in string fields

Thu, 08 Oct 2009 07:02:29 GMT

I have an application that load data from SQL-Server 2000.

Example:

SQL SELECT Myfield1,

Myfield2

FROM MyTable;

The datatype of the field Myfield2 is VARCHAR(10) and contains information such as 1234 and 0001234. However, Qlikview eliminates the leading zeros during load process.

For example: 0001234 is transformed to 1234.

This situation cause a duplicate data in my application.

How can I keep the original format in the data?

Thanks in advance.

Edouard

Leading zeros in string fields

biester — Thu, 08 Oct 2009 10:09:09 GMT

Try this one:

Load Myfield1,text(Myfield2) as Myfield2;
SQL Select Myfield1,Myfield2 from MyTable;

Rgds,
Joachim

Leading zeros in string fields

Thu, 15 Oct 2009 05:52:35 GMT

Biester,

We have already tried this. The problem occurs at the moment we retrieve the data from our SQL 2000 server using an OLEDB Connection. Apparently qlikview assumes that 000123 is the same as 123 and keeps all the data as 123 (sometimes even 000123, apparently it's random). This causes data to be lost, because since the field is of type Varchar 000123 is not the same as 123.

So the problem is on the SQL SELECT Clause, not on the LOAD clause, we tried altering the Connection String to avoid unnecessary fields but it didn't work.

We tried forcing the SQL Statement using the CONVERT(VARCHAR(10), Text1) funciton but it also didn't work.

It's probably a Qlikview related Bug or an option that we either have to set ON or OFF but we can't find any information regarding this issue on qlikview Help or Discussion Boards.

Best Regards

Leading zeros in string fields

Anonymous — Thu, 15 Oct 2009 08:49:49 GMT

Probably it's not the same, but maybe you get something out of it. To keep leading zeros in zip codes, I'm using formatting:
num(Zip, '00000') as Zip

Leading zeros in string fields

rwunderlich — Thu, 15 Oct 2009 09:44:08 GMT

The text() function works for me. I tried the example you provided on SQL2005 like this:

LOAD myInt,
text(myVar) as myVarT,
myVar as myVar;
SQL SELECT myInt,
myVar
FROM robtest.dbo.qvtest;

myVarT showed both "1234" and "0001234". I tried QV 8.50.6299 and 9.0.7119.

Can you post your script or even better a sample qvw?


edouarddgnx wrote:sometimes even 000123, apparently it's random

It's not random. When displaying numbers, QV uses the first string representation of the number it loads as the display. See "Dual" in the reference guide.

-Rob

Leading zeros in string fields

Thu, 15 Oct 2009 17:23:21 GMT

Try load with "_" char in front of the values, to force reading as text, then remove it.

Leading zeros in string fields

Thu, 15 Oct 2009 17:35:15 GMT

Yeah, This is an easier approach.

--Arun

Leading zeros in string fields

Thu, 15 Oct 2009 22:59:50 GMT

Biester,

At a first glance i didn't understand your solution, but when i read it again I understood, I wasn't aware that it was possible to Use a Load Clause in a Select Clause. I made a test application and it worked, we're now altering the original code to check wheter it'll work or not.

Thanks a lot to everyone that helped and posted solutions, soon i'll right if it worked 100%.

Best Regards