topic Re: Trim with if in QlikView

Trim with if

Wed, 30 Nov 2016 17:20:38 GMT

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:

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

Re: Trim with if

maxgro — Wed, 30 Nov 2016 17:26:06 GMT

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

Re: Trim with if

sunny_talwar — Wed, 30 Nov 2016 18:11:01 GMT

You can also do

SubField(Fieldname, ' ', 2) * 1

Re: Trim with if

MarcoWedel — Wed, 30 Nov 2016 22:10:17 GMT

Hi,

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

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

Re: Trim with if

Thu, 01 Dec 2016 10:37:51 GMT

Thanks, this is the only solution that worked 100%

Re: Trim with if

MarcoWedel — Thu, 01 Dec 2016 19:59:00 GMT

You're welcome.

Glad you liked it.

regards

Marco