topic Re: To add zeros in front of fields in order them to be with a length of 6 in QlikView

To add zeros in front of fields in order them to be with a length of 6

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

Hi Members,

Please help me to achieve the said out put. As for my data, I need both EPF Number & Emp fields to be with 6 digit lengths & as long as the length of the record remains less than 6 digits, I need zeros to make it 6 digit lengths.

Eg 3988 to be appeared as 003988 ,once data are loaded. The excel carries this length but once loaded it turns to be a number resulting all zeros getting dropped.

Thanks

Neville

Re: To add zeros in front of fields in order them to be with a length of 6

dplr-rn — Tue, 10 Sep 2019 17:20:12 GMT

change your load script to

LOAD BR_Code, Month, EPF_NO, ''&EMP_NO as EMP_NO, [Employee_ Name], PAID

basically force engine to recognize that column as string

Re: To add zeros in front of fields in order them to be with a length of 6

nevilledhamsiri — Tue, 10 Sep 2019 18:21:54 GMT

Does this add zeros in front of emp_no to appear it with 6 digit lengths?

Could you add more details because I have no idea how to make it

Re: To add zeros in front of fields in order them to be with a length of 6

vikramv — Tue, 10 Sep 2019 18:24:23 GMT

Use repeat function like...

Repeat('0', 6-len(EPF)) & EPF as EPF

Thanks.

Vikky

Re: To add zeros in front of fields in order them to be with a length of 6

nevilledhamsiri — Tue, 10 Sep 2019 18:43:59 GMT

Thanks Vikky,

amazing answer. This is what I needed.

Thanks a lot.

Neville

Re: To add zeros in front of fields in order them to be with a length of 6

Vegar — Tue, 10 Sep 2019 18:46:55 GMT

That's a neat one @vikramv

I usually do like this

Right('000000'& EPF, 6) as EPF,

Or like this if I know that EFT is an integer.

num(EPF, '000000') as EPF,

Re: To add zeros in front of fields in order them to be with a length of 6

vikramv — Tue, 10 Sep 2019 18:52:43 GMT

Welcome !

Re: To add zeros in front of fields in order them to be with a length of 6

nevilledhamsiri — Thu, 12 Sep 2019 17:27:18 GMT

Thanks

Vegar

Fine answer too.

Re: To add zeros in front of fields in order them to be with a length of 6

jduenyas — Thu, 12 Sep 2019 21:31:25 GMT

Right('000000'+EPF,6) AS EPF

Re: To add zeros in front of fields in order them to be with a length of 6

nevilledhamsiri — Fri, 13 Sep 2019 08:38:05 GMT

Thanks Jduenyas

Hlpful answer too