topic Re: Is this possible to store values with leading zero in csv and xls in QlikView

Is this possible to store values with leading zero in csv and xls

Karim_Khan — Wed, 25 Nov 2020 16:16:04 GMT

Hi Team,

I have tried every attempt to store the value with leading zeros in qlik view but all got wasted same is working fine in front end of qlik view.But while am trying to store it in .excel or .csv format it getting removed.If I will open the same with notepad its taking leading zero can somebody is it possible or shall i drop my hope and ?

PFB attempts have tried

1. Text((Repeat('0',5-len(EMP_NO))))AS EMP_NO,

2. if(LEN(EMP_NO)>3,TEXT((Repeat('0',5-len(EMP_NO))&EMP_NO))) AS NEW_NO,

3. replace(ltrim(replace(EMP_NO, '0', ' ')), ' ', 0) as NewMaterial,

4 . IF (LEN(TRIM(EMP_NO))= 5 , '0' & EMP_NO, EMP_NO) AS LEN_NBR,

Regards,

New to QlikView‌

Re: Is this possible to store values with leading zero in csv and xls

felipedl — Wed, 13 Dec 2017 15:47:06 GMT

Hi Karim,

This code:

Load * Inline

[

Data

000001

];

store x into x.txt (txt);

Gets me the attached file.

Can you share what you're doing and some sample data?

Re: Is this possible to store values with leading zero in csv and xls

marcus_sommer — Wed, 13 Dec 2017 15:50:26 GMT

It has nothing to do with Qlik else it's a special excel-feature demonstrating it's very high level intelligence to interpret the content - unfortunately it's not customizable or could be disabled (at least as I know).

As a workaround you could try it with a leading zero-white-space like chr(8203) or the excel own string-interpreter of chr(39) or something similar. By exporting objects from the gui to excel the chr(8203) worked well and I think the logic should work by storings, too.

- Marcus

Re: Is this possible to store values with leading zero in csv and xls

Karim_Khan — Wed, 13 Dec 2017 15:54:48 GMT

Hi Marcus Thanks for ur revert by exporting through GUI its working fine but how to interpret such things its challenge for me now

Re: Is this possible to store values with leading zero in csv and xls

marcus_sommer — Wed, 13 Dec 2017 16:03:31 GMT

Extending the example from Felip I meant the following:

Load chr(8203) & Data as Data Inline

[

Data

000001

];

store x into x.txt (txt);

- Marcus

Re: Is this possible to store values with leading zero in csv and xls

Karim_Khan — Wed, 13 Dec 2017 16:14:29 GMT

Thanks a lot man its done.....

Re: Is this possible to store values with leading zero in csv and xls

jenskrueger — Wed, 10 Jan 2018 20:57:53 GMT

Hallo Marcus,

und wieder einmal hattest du den entscheidenden Tipp: Chr(8203) vor die Nummer setzen bewirkt tatsächlich, dass in Excel die führende Null bestehen bleibt und kein Zeichen davor sichtbar / erkennbar / vorhanden ist. Genauso sollte es sein, super.

Danke dir.

Gruß,

Jens Krüger