Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
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

Tags (1)
1 Solution

Accepted Solutions

Re: Trim with if

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
MVP
MVP

Re: Trim with if

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

Re: Trim with if

You can also do

SubField(Fieldname, ' ', 2) * 1

Re: Trim with if

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

Not applicable

Re: Trim with if

Thanks, this is the only solution that worked 100%

Re: Trim with if

You're welcome.

Glad you liked it.

regards

Marco