Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
hic
Former Employee
Former Employee

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
swuehl
MVP
MVP

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
Author

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

ashfaq_haseeb
Champion III
Champion III

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
Author

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

the aim is to find its reason

hic
Former Employee
Former Employee

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

Not applicable
Author

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

hic
Former Employee
Former Employee

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
Author

Ok....i understand now...

Yes,it is actually exported from an external system.

Thank you very much !!!