topic Re: Keeping Leading Zeros in QlikView

Keeping Leading Zeros

Thu, 09 Apr 2015 10:03:21 GMT

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

Re: Keeping Leading Zeros

Fri, 10 Apr 2015 13:27:13 GMT

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).

Re: Keeping Leading Zeros

rwunderlich — Wed, 22 Apr 2015 21:52:38 GMT

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 [

001

002

];

A List Box for X will show:

001

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

Re: Keeping Leading Zeros

Anonymous — Wed, 22 Apr 2015 22:18:49 GMT

Maybe like this in QV?

Load

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

Re: Keeping Leading Zeros

Tue, 02 Jun 2015 14:37:54 GMT

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

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

Re: Keeping Leading Zeros

Gabriel — Tue, 02 Jun 2015 14:53:04 GMT

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

Re: Keeping Leading Zeros

Tue, 02 Jun 2015 15:03:46 GMT

Hi,

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

Re: Keeping Leading Zeros

Gabriel — Tue, 02 Jun 2015 15:07:35 GMT

In that case do this

SummaryOrders:

LOAD

;

SQL SELECT OrderNumber

,Product

FROM <Table>;

STORE SummaryOrders INTO D:\Orders.qvd;

Re: Keeping Leading Zeros

rubenmarin — Tue, 02 Jun 2015 15:10:35 GMT

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.

Re: Keeping Leading Zeros

Tue, 02 Jun 2015 15:40:19 GMT

Thanks, this has worked a treat!

Re: Keeping Leading Zeros

Gabriel — Tue, 02 Jun 2015 15:45:56 GMT

Hi,

Which suggestion worked for you?

Can you possibly mark as correct for anyone who might have similar question

Re: Keeping Leading Zeros

Tue, 02 Jun 2015 15:52:38 GMT

It was Ruben's suggestion of using the Text function in the Load that worked.

Thanks

Re: Keeping Leading Zeros

julian_rodriguez — Wed, 30 Aug 2017 21:55:01 GMT

Hello Rob

What if I need to use the wildcard (*) and one of this fields have both '01' and '1' values, and I want to preserve them?

For example:

LOAD * From Table;

If you are asking why I do need the wilcard, is for a recursive code for incremental loading.

How do I disable this QV "auto field type detection"?

Thanks in advance

Julian

Re: Keeping Leading Zeros

rwunderlich — Wed, 30 Aug 2017 22:52:27 GMT

Julian,

You can't disable the behavior, except by using Text(). So the workaround for LOAD * (example field "Product") is:

LOAD

Text(Product) as Product2

FROM source...;

DROP FIELD Product;

RENAME FIELD Product2 TO Product;

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Re: Keeping Leading Zeros

julian_rodriguez — Wed, 30 Aug 2017 22:58:09 GMT

I see... well, thanks Rob!

Re: Keeping Leading Zeros

brooksc57 — Sun, 17 Dec 2017 05:20:11 GMT

Not exactly sure why Qlik would have this as a feature.

as pointed out...it would appear that it uses the first one it finds...

thought this visual might save someone time.

the second box is the raw text with a plc\ prefix so it is readable as was intended.

the first box is what happened to it on import...since 016 was first, 16 became 016.

quite a feature ??????