Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Not applicable

QV adds leading zeros

Hi,

I've an excel file having a field named Emp having values like 0,100001, 100037, 100038...etc...

when i do:

load *, len(Emp) as LenEmp from xls;

i get the correct data in Emp but LenEmp=8 for every Emp value( even for Emp=0...strange !!!)

stored that table in qvd....

I used len(Emp) because when i stored the data in a qvd & using that qvd data i got Emp values as 00000000 for Emp=0,

00100001,00100037,00100038 etc.. i.e  the total length is 8.

What could be the reason behind this strange behaviour ?

loading field using text(Emp) also does the same.....using num(Emp) gives correct result but i don't want to use it as a number.

Regards

1 Solution

Accepted Solutions

Re: QV adds leading zeros

The numbers do indeed have eight positions in the Excel file, so Len(PERNR) should be 8.

Then QlikView strips the leading zeros. If you want it formatted usning 8 positions you should use

Num(PERNR,'00000000')

HIC

Image1.png

View solution in original post

8 Replies
MVP
MVP

Re: QV adds leading zeros

Could you post a small sample of this excel file?

Is the field in the Excel already formatted as string and may contain leading / trailing spaces or non-visible chars?

Not applicable

Re: Re: QV adds leading zeros

Hi,

please find attached the excel file & below is the LOAD script

LOAD MANDT,

     VBELN,

     POSNR,

     PARVW,

     KUNNR,

     LIFNR,

     PERNR,

    len(PERNR) as len

len comes out to be 8 for each PERNR

thanks

Re: Re: Re: QV adds leading zeros

Hi,

try the below script.

Directory;

LOAD MANDT,

     VBELN,

     POSNR,

     PARVW,

     KUNNR,

     LIFNR,

     PERNR,

     text(num(PERNR)) as num,

     PARNR,

     ADRNR,

     ABLAD,

     LAND1,

     ADRDA,

     XCPDK,

     HITYP,

     PRFRE,

     BOKRE,

     HISTUNR,

     KNREF,

     LZONE,

     HZUOR,

     STCEG,

     PARVW_FF,

     ADRNP,

     KALE

FROM

[testfile - Copy.xlsx]

(ooxml, embedded labels, table is [VBPA 20140424 at 1315]);

This should solve your problem

Regards

ASHFAQ

Not applicable

Re: Re: Re: QV adds leading zeros

the aim here is not to solve the problem....

the aim is to find its reason

Re: QV adds leading zeros

The numbers do indeed have eight positions in the Excel file, so Len(PERNR) should be 8.

Then QlikView strips the leading zeros. If you want it formatted usning 8 positions you should use

Num(PERNR,'00000000')

HIC

Image1.png

View solution in original post

Not applicable

Re: QV adds leading zeros

Hello Henric,

Thanks for the answer.

Why does it add extra zeros....is there any setting somewhere in excel that makes the total length as 8 ?

Thanks

Re: QV adds leading zeros

It does not add extra zeros. They exist in the Excel document already - see picture in my answer above.

But when I open the file in Excel, Excel strips the leading zeros. So I suspect that you have created the Excel file with another tool than Excel; one that creates the numbers with 8 positions.

HIC

Not applicable

Re: QV adds leading zeros

Ok....i understand now...

Yes,it is actually exported from an external system.

Thank you very much !!!