topic Re: Help with leading zero expression in QlikView

Help with leading zero expression

helen_pip — Thu, 06 Oct 2016 07:49:10 GMT

Dear Qlikview user

I have a field which is a unique identifier. The unique identifier starts from 1 digit up to 7 digits, so for those values which have just one number I wanted to pad out the string with zero's

I.e. Identifier 799 would be padded out to be 0000799

I have used the following function to create this effect:

repeat('0', (7 - len([Field))) & [Field] as Unique_ID

This technique works and pads out the field, however it is also adding a 0 to values which are already 7 digits long

I.e. 10080866 should be 1080866

For the life of me I cannot see why the function is doing this

Any help would be greatly appreciated

Thanks

Helen

Re: Help with leading zero expression

adamdavi3s — Thu, 06 Oct 2016 07:54:11 GMT

Hi Helen,

That is really weird as it seems to work fine for me?

Re: Help with leading zero expression

zhadrakas — Thu, 06 Oct 2016 07:58:34 GMT

Change from

repeat('0', (7 - len([Field))) & [Field] as Unique_ID

repeat('0', (7 - len( [Field] )) & [Field] as Unique_ID

Re: Help with leading zero expression

helen_pip — Thu, 06 Oct 2016 08:01:47 GMT

Hi Tim

Thank you for the response

I must admit, that was just a bad copy and paste job

Thanks

Helen

Re: Help with leading zero expression

helen_pip — Thu, 06 Oct 2016 08:02:57 GMT

Hello Adam

Umm! I am pleased to hear that it is not necessarily the function...I will look into my data some more

Thanks

Helen

Re: Help with leading zero expression

adamdavi3s — Thu, 06 Oct 2016 08:05:25 GMT

Hi Helen,

Hmmm it is weird to see how it could be a data issue though, as if it had trailing spaces etc you would expect the length to be more than 7 so it wouldn't do anything...

Interested to know though!

Re: Help with leading zero expression

tamilarasu — Thu, 06 Oct 2016 08:09:59 GMT

Hi Helen,

You can also use Num function

Num(Field,'0000000')

Re: Help with leading zero expression

el_aprendiz111 — Thu, 06 Oct 2016 14:23:52 GMT

HI,

if(Len([Field])<7,Repeat(0,7-Len([Field]))&[Field],Left([Field],7)) AS [<=7],