Keeping Leading Zeros

[#1]

Hi,

I am currently having an issue whereby leading zeros are sometimes being dropped, not all the time, when importing data from SQL.  We have tried CAST from the SQL side and TEXT in Qlikview with no success. 

The only short-term fix we can think of is to add a character, a-z, to the field and import it to force it into a alpha field.  We would then need to remove it at the last stage.

Has anyone else had this issue and did you find a solution?

Thanks

Adam

[#2]

Adam,

QlikView doesn't inherently remove leading zeroes. The QlikView engine doesn't really have datatypes so it treats most everything like a text field. Most likely, the driver you're using to talk to your SQL DB is optimizing or getting rid of them.

I've also seen where people will export the data to CSV and Excel will remove leading zeroes when it formats a cell (if that's what is happening for you).

[rwunderlich]

Qlikview does internally treat "001" and "1" as the same value. However, the display representation will be whatever string it loads first for that value. For example, given:

LOAD * INLINE [

X

001

01

1

02

2

002

];

A List Box for X will show:

001

02

So it looks like leading zeros are not being stripped, and it's a bit random dependent on load orders. If you want to force removal of leading zeros in the display, use the num() function when loading:

num(X) as X

If you want to preserve 001, 01 and 1 as different values, use the text function when loading:

text(X) as X

-Rob

http://masterssummit.com

http://robwunderlich.com

[Anonymous]

Maybe like this in QV?

Load

right('000000000000'&FIELD1,12) as FIELD1  // to make it a 12 digit number, change this number accordingly

[#5]

Thanks Rob for your reply and explanation.  I now understand the principals but I am still having issues.  Hopefully I can explain a bit more.

The data is coming from SQL Server and we are loading the data using an ODBC connection and the following code:

SummaryOrders:

SQL SELECT OrderNumber

          ,Product

FROM <Table>;

STORE SummaryOrders INTO D:\Orders.qvd;

The data starts as

OrderNumber     Product

8100001-001          00022

8100001-001          22

8100002-001          14

8100002-001          00014

But when selecting from the qvd it appears as

OrderNumber     Product

8100001-001          00022

8100001-001          00022

8100002-001          14

8100002-001          14

So, QlikView is working as you said, and taking the format from the first instance of each Product, i.e. 00022 and 14. 

How do I get this pull through like-for-like?  The only way I have thought of is to add an extra field in the SELECT statement:

SummaryOrders:

SQL SELECT OrderNumber

          ,Product

          ,'a'+Product AS NewProduct

FROM <Table>;

An additional step would then be needed as follows:

NewOrders:

LOAD OrderNumber

          ,TEXT(MID(NewProduct,2)) AS Product

RESIDENT SummaryOrders;

Is this the only way to do it?

Thanks

Adam

[Gabriel]

Hi,

I think there's trimming going on. in that case do the following and let us know the result

Set this variable at the beginning of your QlikVIew script

set Verbatim = 1 // Turn off trim

Set this at the end of your script.

set Verbatim = 0 // Turn on trim

[#7]

Hi,

Thanks, I've just tried this and it doesn't make a difference.  It is still trimming the leading zero's

[Gabriel]

In that case do this

SummaryOrders:

LOAD

*

;

SQL SELECT OrderNumber

          ,Product

FROM <Table>;

STORE SummaryOrders INTO D:\Orders.qvd;

[rubenmarin]

Hi Adam, I usually made it retrieving the field with Text(), worked for me, try:

SummaryOrders:

LOAD OrderNumber,

     Product

     Text(Product) as NewProduct

SQL SELECT OrderNumber, Product

FROM <Table>;

And check if NewProduct keeps the original value.

[#10]

Thanks, this has worked a treat!