Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Tatynout
Contributor III
Contributor III

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

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

1 Solution

Accepted Solutions
Rohan
Specialist
Specialist

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.

View solution in original post

4 Replies
Rohan
Specialist
Specialist

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
Partner - Specialist
Partner - Specialist

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
Partner - Champion III
Partner - Champion III

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

=Num(Value, '0000000000')

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Tatynout
Contributor III
Contributor III
Author

It works perfectly. Thank you! 🙂