Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Only get certain characters from a field

Hi

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?

Value

ID

MCS_NY_2523_B12

B12

INC_NJ_2522_A1

A1

RETDS_BY_LO__TIM_S130

S130

1 Solution

Accepted Solutions
jschrader
Contributor III
Contributor III

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

View solution in original post

7 Replies
jschrader
Contributor III
Contributor III

=subfield(Value, '_', 4)

antoniotiman
Master III
Master III

SubField(Value,'_',-1)

Digvijay_Singh

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
Partner - Creator III
Partner - Creator III

LOAD Value

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

FROM <DataSource>

jschrader
Contributor III
Contributor III

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
Master III
Master III

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

inline

[

Value

MCS_NY_2523_B12

INC_NJ_2522_A1

RETDS_BY_LO__TIM_S130

];

Not applicable
Author

Try below:

subfield(FieldName ,'_' ,-1)