topic Re: Return everything before number in a string in QlikView

Return everything before number in a string

Fri, 26 Jun 2015 09:41:15 GMT

Hi,

i have an alphanumeric field where i want to return everything left and including the first number.

For example.

Field1

Java 3 patch 1

Java 6 version

Java patch 3

Java 7 version

Java 7 (TM) 123

i want to return

Field1

Java 3

Java 6

Java patch 3

Java 7

i think i need to use the left and the subfield functions but i don't know how to say return anything left of the first number you find.

Any help would be great.

thanks

Re: Return everything before number in a string

swuehl — Fri, 26 Jun 2015 09:47:54 GMT

Maybe like

LOAD

Field1,

Left( Field1, FindOneOf( Field1,'0123456789') ) as LeftPart,

...

FROM YourTable;

Re: Return everything before number in a string

tresB — Fri, 26 Jun 2015 09:49:43 GMT

Try like:

=Left(Field1,FindOneOf(Field1, '0123456789'))

Re: Return everything before number in a string

Fri, 26 Jun 2015 10:01:42 GMT

Hi ,

This works but if there is a is no number it returns a blank.

Is there a way to say if there is no number to return the string itself

E.G

Field1

Java 3 patch 1

Java patch

Java 6 version

Java patch 3

java

Java 7 version

Java 7 (TM) 123

i want to return

Field1

Java 3

java patch

Java 6

Java patch 3

java

Java 7

Re: Return everything before number in a string

Fri, 26 Jun 2015 10:02:23 GMT

Hi ,

This works but if there is a is no number it returns a blank.

Is there a way to say if there is no number to return the string itself

E.G

Field1

Java 3 patch 1

Java patch

Java 6 version

Java patch 3

java

Java 7 version

Java 7 (TM) 123

i want to return

Field1

Java 3

java patch

Java 6

Java patch 3

java

Java 7

Re: Return everything before number in a string

morganaaron — Fri, 26 Jun 2015 10:12:12 GMT

Not particularly elegant but:

=Left(Field1, If(FindOneOf(Field1, '0123456789') > 0, FindOneOf(Field1, '0123456789'), Len(Field1)))

Would work.

Re: Return everything before number in a string

Fri, 26 Jun 2015 10:23:34 GMT

thanks everyone for helping

final solution:

if(FindOneOf(field1,'1234567890')=0, field1, (left(field1, FindOneOf(field1,'1234567890'))))