topic Re: QV adds leading zeros in QlikView

QV adds leading zeros

Sat, 10 May 2014 06:41:59 GMT

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

Re: QV adds leading zeros

swuehl — Sat, 10 May 2014 09:43:09 GMT

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?

Re: Re: QV adds leading zeros

Mon, 12 May 2014 04:32:15 GMT

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

ashfaq_haseeb — Mon, 12 May 2014 05:52:12 GMT

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

Re: Re: Re: QV adds leading zeros

Mon, 12 May 2014 05:55:31 GMT

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

the aim is to find its reason

Re: QV adds leading zeros

hic — Mon, 12 May 2014 08:49:37 GMT

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

Re: QV adds leading zeros

Mon, 12 May 2014 08:58:21 GMT

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

hic — Mon, 12 May 2014 09:04:57 GMT

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

Re: QV adds leading zeros

Mon, 12 May 2014 09:07:42 GMT

Ok....i understand now...

Yes,it is actually exported from an external system.

Thank you very much !!!