Discussion board where members can get started with QlikView.
I have a field called Option_Code whose value is a string that I'd like to separate out.
All I want is everything from the left of the text to be deleted so that it looks like this:
I'm assuming i'll need to use a trim function, but i'm not sure how the coding should be..
I tried using
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!
Go to Solution.
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
there are probably several ways to do this, I think you can do it like:
subfield(Option_Code,'_') as SubCode,
recno() as RecNo,
rowno() as SortOrder
LOAD concat(SubCode,'_',SortOrder) as Option_Code resident TMP where isText(SubCode) group by RecNo;
drop table TMP;
let vAlphaChars = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
mid(Option_Code, findoneof(Option_Code,'$(vAlphaChars)')) as YourfieldName,
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
It would be best to create a new field in the script, using any of the suggested solutions (I would prefer mwoolf's one)
mid(Option_Code, findoneof(Option_Code,'$(vAlphaChars)')) as Option_Box,