topic Re: Separate a String in QlikView

Separate a String

Fri, 12 Oct 2012 18:02:04 GMT

Hello All!,

I have a field called Option_Code whose value is a string that I'd like to separate out.

Option_Code:

0500_0599_VALUE_PKG

9078_FENDER_LIGHTS

9188_ENGINE_BLK_HTR_110VG

0001_0249_DESTN_COUNTRY

All I want is everything from the left of the text to be deleted so that it looks like this:

VALUE_PKG

FENDER_LIGHTS

ENGINE_BLK_HTR_110VG

DESTN_COUNTRY

I'm assuming i'll need to use a trim function, but i'm not sure how the coding should be..

I tried using

=purgechar([OPTION BOX],'_')

which helps get rid of the underscores '_'

but i'd like to only have the TEXT showing ( not the beginning #'s)

Thank you for any help!

-Liz

Re: Separate a String

Fri, 12 Oct 2012 18:41:18 GMT

Try something like this:

if(Left(Right(PurgeChar(Value,'0123456789'),Len(PurgeChar(Value,'0123456789'))-1),1)='_',Right(PurgeChar(Value,'0123456789'),
Len(PurgeChar(Value,'0123456789'))-2),Right(PurgeChar(Value,'0123456789'),Len(PurgeChar(Value,'0123456789'))-1)) as Final

See attached.

Re: Separate a String

swuehl — Fri, 12 Oct 2012 18:41:45 GMT

Hi Liz,

there are probably several ways to do this, I think you can do it like:

TMP:

LOAD

subfield(Option_Code,'_') as SubCode,

recno() as RecNo,

rowno() as SortOrder

INLINE [

Option_Code

0500_0599_VALUE_PKG

9078_FENDER_LIGHTS

9188_ENGINE_BLK_HTR_110VG

0001_0249_DESTN_COUNTRY

];

RESULT:

LOAD concat(SubCode,'_',SortOrder) as Option_Code resident TMP where isText(SubCode) group by RecNo;

drop table TMP;

Re: Separate a String

m_woolf — Fri, 12 Oct 2012 18:55:01 GMT

Try:

let vAlphaChars = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'

mid(Option_Code, findoneof(Option_Code,'$(vAlphaChars)')) as YourfieldName,

Re: Separate a String

Mon, 15 Oct 2012 15:08:22 GMT

Thank you for all your help!

I was able to have a separate column within the Option Code listbox, however i'd like to make a separate listbox with just the Option Box name

Re: Separate a String

swuehl — Tue, 16 Oct 2012 13:50:39 GMT

It would be best to create a new field in the script, using any of the suggested solutions (I would prefer mwoolf's one)

let vAlphaChars = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'

LOAD

Option_Code,

mid(Option_Code, findoneof(Option_Code,'$(vAlphaChars)')) as Option_Box,

...

from YourTable;