Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi Helen,
That is really weird as it seems to work fine for me?
Change from
repeat('0', (7 - len([Field))) & [Field] as Unique_ID
to
repeat('0', (7 - len( [Field] )) & [Field] as Unique_ID
Hi Tim
Thank you for the response
I must admit, that was just a bad copy and paste job
Thanks
Helen
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
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!
Hi Helen,
You can also use Num function
Num(Field,'0000000')
HI,
if(Len([Field])<7,Repeat(0,7-Len([Field]))&[Field],Left([Field],7)) AS [<=7],