Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

nsnybs21qv
New Contributor II

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

Tags (1)
1 Solution

Accepted Solutions
jschrader
New Contributor III

Re: Only get certain characters from a field

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

7 Replies
jschrader
New Contributor III

Re: Only get certain characters from a field

=subfield(Value, '_', 4)

antoniotiman
Honored Contributor III

Re: Only get certain characters from a field

SubField(Value,'_',-1)

Digvijay_Singh
Honored Contributor III

Re: Only get certain characters from a field

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

Re: Only get certain characters from a field

LOAD Value

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

FROM <DataSource>

jschrader
New Contributor III

Re: Only get certain characters from a field

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
Honored Contributor III

Re: Only get certain characters from a field

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

Re: Only get certain characters from a field

Try below:

subfield(FieldName ,'_' ,-1)

Community Browser