topic Qlikview load appending zero to front in QlikView

Qlikview load appending zero to front

chris1987 — Tue, 20 Mar 2018 23:22:36 GMT

Hi,

I'm running a really simple script that imports items numbers and descriptions. There is a chance that some of the item numbers have a leading zero and some don't e.g. 12345 and 012345

The problem is that QV is adding a leading zero to 12345, turning it into item number 012345 which is a different part number.

Here's the code:

Item:

SQL SELECT

"In-entity",

"Item-no",

"Uom-code",

"Description",

"Active",

"res-str",

"stock-item"

FROM MYTABLE.PUB."item" ';

store Item into D:\MyFile\Item.qvd (qvd);

I've also tried adding cast which didn't work either:

Item:

SQL SELECT

"In-entity",

CAST("Item-no" as varchar(20)) as "Item-no",

"Item-no",

"Uom-code",

"Description",

"Active",

"res-str",

"stock-item"

FROM MYTABLE.PUB."item" ';

store Item into D:\MyFile\Item.qvd (qvd);

Please can anyone advise is they have seen this before? We've just upgraded to v12.2 and I can't remember experiencing it on an older version.

Thanks

Chris

Re: Qlikview load appending zero to front

vinieme12 — Wed, 21 Mar 2018 02:25:21 GMT

Load them as Text then

Item:

LOAD

In-entity,

TEXT(Item-no) as ItenNo,

Uom-code,

Description,

Active,

res-str,

stock-item;

SQL SELECT

"In-entity",

"Item-no",

"Uom-code",

"Description",

"Active",

"res-str",

"stock-item"

FROM MYTABLE.PUB."item" ';

Re: Qlikview load appending zero to front

avinashelite — Wed, 21 Mar 2018 06:40:51 GMT

if your field is in proper number format then use NUM() function around that should resolve the issue

Re: Qlikview load appending zero to front

Chanty4u — Wed, 21 Mar 2018 06:44:25 GMT

or may be

REPLACE(LTRIM(REPLACE(Field, '0', ' ')), ' ', '0')

Re: Qlikview load appending zero to front

chris1987 — Wed, 21 Mar 2018 10:06:19 GMT

Hi,

Unfortunately this doesn't work - the value already has the "0" appended on the SQL Select

Chris

Re: Qlikview load appending zero to front

chris1987 — Wed, 21 Mar 2018 10:06:51 GMT

Hi, Unfortunately the field is alphanumeric (part numbers like R12345 also exist)

Re: Qlikview load appending zero to front

chris1987 — Wed, 21 Mar 2018 10:07:26 GMT

Hi, Some part numbers genuinely need the "0" at the beginning.