set data value's length base on it's current lenght

[Tatynout]

Hello,

I would like the apply a condition on data values from my Vendor field. I want to set each values in that field so they all have 10 characters and to do so i need to apply a condition in such a way that during the script load, if the value's lenght is smaller than 10 then 0 should be added in front of the value until it makes 10 characters.

For Example:

Vendor

0045676

000034567

76890650

2340987653

I should get the following results:

Vendor

0000045676

0000034567

0076890650

2340987653

Thanks for your help.

Regards,

Tatiana

[Rohan · Accepted solution]

Hello,

I have tried this on the given Sample & it worked. Can you also try it out ?

ABC :
Load * Inline [
Vendor
0045676
000034567
76890650
2340987653
];

NoConcatenate

DEF :
Load
Vendor,
Len(Vendor) as len,
Pick(10-Len(Vendor),'0','00','000','0000','00000','000000','0000000','00000000','000000000','0000000000')&Vendor as new
Resident ABC ;
Drop Table ABC;
exit Script;

You only need that Highlighted Statement to get the Output.

You can even make this Dynamic by setting that '10' into a variable & then calling that variable in the Pick Statement.

Let me know if you have any Queries or if this didn't work.

Thanks & Regards 

Rohan S. Desai.

[Mauritz_SA]

Hi there

What @Rohan  said will work. If you want to make it a bit more dynamic you can use repeat as well:

Repeat('0',10 - Len(Vendor)) & Vendor AS new

As Rohan mentioned, you can always use a variable set the number of characters you need the output to be and you can even look for the value with the most characters and make sure that it is the greatest value between that value and 10 (or the value in your variable).

Regards,

Mauritz

[jonathandienst]

If the values are all numeric, just use Num to left pad with zeros:

=Num(Value, '0000000000')

[Tatynout]

It works perfectly. Thank you! 🙂