Qlikview load appending zero to front

[chris1987]

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

[vinieme12]

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" ';

[avinashelite]

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

[Chanty4u]

or may be

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

[chris1987]

Hi,

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

Chris

[chris1987]

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

[chris1987]

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