Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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?
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
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
the aim here is not to solve the problem....
the aim is to find its reason
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
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
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
Ok....i understand now...
Yes,it is actually exported from an external system.
Thank you very much !!!