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: 
Not applicable

Trim with if

Hi I have a field like so:

test 001

test 002

test 010

test 013

test 234

I need to create a new column but format the values so I am only left with the whole numbers with no proceeding zero's:

1

2

10

13

234

I'm assuming I use the trim function with an if statement but just struggling to get it to work. Any idea's?

Thanks

1 Solution

Accepted Solutions
MarcoWedel

Hi,

another solution to only get the numerical value of the last consecutive number block could be:

QlikCommunity_Thread_241611_Pic1.JPG

LOAD *,

    Num(Mid(String,FindOneOf(String, PurgeChar(String,'-0123456789'), -1)+1)) as Number

INLINE [

String

test 001

test 002

test 010

test 013

test 234

Text With Four Spaces 012345

TextWith Three Spaces 0012346

TextWithTwo Spaces 00012347

TextWithOneSpace 000012348

TextWithoutSpaces0000012349

5

06

070

Just a textwithout numbers

];

hope this helps

regards

Marco

View solution in original post

5 Replies
maxgro
MVP
MVP

num(SubField(yourfield, ' ', 2))

sunny_talwar

You can also do

SubField(Fieldname, ' ', 2) * 1

MarcoWedel

Hi,

another solution to only get the numerical value of the last consecutive number block could be:

QlikCommunity_Thread_241611_Pic1.JPG

LOAD *,

    Num(Mid(String,FindOneOf(String, PurgeChar(String,'-0123456789'), -1)+1)) as Number

INLINE [

String

test 001

test 002

test 010

test 013

test 234

Text With Four Spaces 012345

TextWith Three Spaces 0012346

TextWithTwo Spaces 00012347

TextWithOneSpace 000012348

TextWithoutSpaces0000012349

5

06

070

Just a textwithout numbers

];

hope this helps

regards

Marco

Not applicable
Author

Thanks, this is the only solution that worked 100%

MarcoWedel

You're welcome.

Glad you liked it.

regards

Marco