topic Re: Only get certain characters from a field in QlikView

Only get certain characters from a field

Anonymous — Wed, 12 Aug 2015 21:04:13 GMT

I have a field with values shown below. Is there a way to write a script to only get the ID after the last '_' sign as shown in the ID column?

jschrader — Wed, 12 Aug 2015 21:13:01 GMT

=subfield(Value, '_', 4)

antoniotiman — Wed, 12 Aug 2015 21:15:30 GMT

SubField(Value,'_',-1)

Digvijay_Singh — Wed, 12 Aug 2015 21:15:49 GMT

Many ways to do it, as of now I could get - Get the index of last '_', minus it from total length and extract it from right..

=right('INC_NJ_2522_A1',len('INC_NJ_2522_A1')-index('INC_NJ_2522_A1','_',-1) )

maximiliano_vel — Wed, 12 Aug 2015 21:23:03 GMT

LOAD Value

Right(Value, LEN(Value) - FindOneOf(Value,'_',SubStringCount(Value, '_'))) as ID

FROM <DataSource>

jschrader — Wed, 12 Aug 2015 21:28:39 GMT

You do need to use index if you have varying number of _. I did not see that the first time. Nicole is right.

=right(Value,len(Value)-Index(Value,'_',-1))

I need to stop getting excited because I know the answer and read the full post first

sasiparupudi1 — Thu, 13 Aug 2015 06:02:09 GMT

load Value,SubField(Value,'_',SubStringCount(Value,'_')+1) as ID

inline

[

Value

MCS_NY_2523_B12

INC_NJ_2522_A1

RETDS_BY_LO__TIM_S130

];

Thu, 13 Aug 2015 18:45:29 GMT

Try below:

subfield(FieldName ,'_' ,-1)