topic Re: split string value in QlikView

split string value

micheledenardi — Mon, 02 Feb 2015 16:12:54 GMT

i have some strings like:

'this is the first string 5862'

'this is the second string 0'

'this is the third string 23'

and i want to separate every string in two parts, one for the text of the string and one for the numeric part to obtain:

'this is the first string' '5862'

'this is the second string' '0'

'this is the third string' '23'

can someone help me ?

thank's

Re: split string value

maxgro — Mon, 02 Feb 2015 16:17:53 GMT

hope to understand

number is always the last word

blank between words

RESULT:

SCRIPT:

load string, left(string, index(string,number)-1) as text, number;

load *, subfield(string, ' ', -1) as number inline [

string

'this is the first string 5862'

'this is the second string 0'

'this is the third string 23'

];

Re: split string value

rubenmarin — Mon, 02 Feb 2015 16:20:38 GMT

Hi Michele, It's always text + blank_space + number? In this case you can use:

First part: Left(StringField, Index(StringField, ' ', -1))

Second part: Mid(StringField, Index(StringField, ' ', -1)+1)

or: Subfield(StringField, ' ', -1)

Re: split string value

Mon, 02 Feb 2015 16:24:05 GMT

Hi Michele,

if there isn't always a set format in the values, extra spaces etc etc

then you could use purse and keep char functions

KeepChar([myField],'0123456789') AS number

PurgeChar([myField],'0123456789') AS text

hope that helps

Joe

Re: split string value

micheledenardi — Mon, 02 Feb 2015 16:27:17 GMT

hi ruben, no, the string can be composed by text+blank space+number +text + number and i want to separate the final number from the rest of the string

Re: split string value

micheledenardi — Mon, 02 Feb 2015 16:38:04 GMT

hi joe,

i can't use keep and purse funcion because my string are composed like

'this is string1 0'

'this is string 2 58'

this is string 3 5869'

and i want to retrive

'this is string1' '0'

'this is string 2' '58'

this is string 3' '5869'

Re: split string value

maxgro — Mon, 02 Feb 2015 16:51:15 GMT

this is the result (text and numberfields) using my previous answer