topic Re: Adding spaces to a field value in QlikView

Adding spaces to a field value

Tue, 31 Jul 2012 11:22:13 GMT

Hello all,

I have been searching the community but didn't find an answer.

I'm loading a field with a numeric value, but it can be considered as a string as it is a code which I display in the front-end.

Some values have a length of less then 6 characters.

They should at minimum be 6 characters. It could be they have more then 6 characters.

How do I do this in the script, adding a prefix (spaces) so the figure has 6 characters unless the figure is 6 or more then 6 long?

Example '123' should become ' 123' but '1234567' should stay '1234567'

Thanks in advance for your help

Niels

Tue, 31 Jul 2012 11:39:35 GMT

num(number1,' ') as number2

Tue, 31 Jul 2012 11:40:21 GMT

if(len(number1)>5,number1,num(number1,' ')) as number2

daveamz — Tue, 31 Jul 2012 11:41:22 GMT

Hello,

Try something like:

if(len(field)<6, repeat(' ',(6-len(field)))&field, field) as field2

See the example attached.

Good luck!

jonathandienst — Tue, 31 Jul 2012 11:44:10 GMT

I think this is simpler:

=right(' ' & field, 7)

Regards

Jonathan

daveamz — Tue, 31 Jul 2012 12:03:22 GMT

Right, but what if the field has 8 or more characters? Then '12345678' will become '1234567'

jonathandienst — Tue, 31 Jul 2012 12:09:41 GMT

Well, if you want to pad out to 8 characters, then

=right(' ' & field, 😎

It all depends on the data specification...

Jonathan