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: 
Vinod
Creator
Creator

Adding Zero's

Hi All,

How to add Zero's in between characters and Numbers,

Please find the attachment for your reference.

Thanks

Vinod,

2 Solutions

Accepted Solutions
tresesco
MVP
MVP

Temp:
Load *,
Mid(Vin_No,1,FindOneOf(Vin_No, '0123456789')+1) as NumPart

Inline [
Vin_No
MBJ123
MBJ1234
MBJ12345
];

Load
Max(Len(NumPart)) as MaxNum
Resident Temp;

Let vNumLength = Peek('MaxNum');
Let vNumFormat = chr(39)& Repeat('0',vNumLength) & chr(39) ;

Final:
Load
Vin_No,
Mid(Vin_No,1,FindOneOf(Vin_No, '0123456789')-1) & Num(KeepChar(Vin_No, '0123456789'), $(vNumFormat)) as Formatted_Vin_No
Resident Temp;

DROP table Temp



Try something like above.

View solution in original post

tresesco
MVP
MVP

In this new community it is hard to find whom you replied to. Which solution is working for you, the static one Avinash suggested or the one I suggested? Please like (it's important now 🙂 ), mark helpful/correct the posts you feel so and close the thread.

View solution in original post

7 Replies
tresesco
MVP
MVP

In script or UI?
Vinod
Creator
Creator
Author

In Script Only

tresesco
MVP
MVP

Temp:
Load *,
Mid(Vin_No,1,FindOneOf(Vin_No, '0123456789')+1) as NumPart

Inline [
Vin_No
MBJ123
MBJ1234
MBJ12345
];

Load
Max(Len(NumPart)) as MaxNum
Resident Temp;

Let vNumLength = Peek('MaxNum');
Let vNumFormat = chr(39)& Repeat('0',vNumLength) & chr(39) ;

Final:
Load
Vin_No,
Mid(Vin_No,1,FindOneOf(Vin_No, '0123456789')-1) & Num(KeepChar(Vin_No, '0123456789'), $(vNumFormat)) as Formatted_Vin_No
Resident Temp;

DROP table Temp



Try something like above.

avinashelite

If it always starts with MBJ* Try like this 

 

left(Vin_No,3)&0&keepchar(Vin_No,'0123456789') as Test 

 

Vinod
Creator
Creator
Author

Thank You, It's working.

tresesco
MVP
MVP

In this new community it is hard to find whom you replied to. Which solution is working for you, the static one Avinash suggested or the one I suggested? Please like (it's important now 🙂 ), mark helpful/correct the posts you feel so and close the thread.

avinashelite

Hi Vinod , could you please like the solution which helped you to resolve the issue ...with the new community site we don't have any ways to mark the thread as answered ..