First two characters on the left

[Anonymous]

Hi,

I am trying to extract the first two characters on the left of the first forward slash, "/", in a string.

e.g if my strings were:

isisvfveirf/ibei

ertige.veugeu/iei

eqpd/iebfie/wiffr

weuifw.if/wiiwf.iwbi.ieruf

Then I would like to extract

rf

eu

ie

if

I have managed to do this in excel using the following formula but can not seem to do it in qlikview:

=MID(CELL,FIND("/",CELL)-2,2)

Can someone help me to translate this into qlikview as I can not seem to find the equivalent

[sunny_talwar · Accepted solution]

May be this

Right(SubField(FieldName, '/', -2), 2) as FieldName2

[sunny_talwar]

Here is a sample script with inline load of the values you gave

Table:

LOAD *,

Right(SubField(FieldName, '/', -2), 2) as FieldName2;

LOAD * INLINE [

    FieldName

    isisvfveirf/ibei

    ertige.veugeu/iei

    eqpd/iebfie/wiffr

    weuifw.if/wiiwf.iwbi.ieruf

];

[vishalgoud]

Hi , try the below

=right(SubField(PurgeChar(SubField('ertige.veugeu/iei','/'),'()'),' '),2)

[swuehl]

The direct translation of the Excel formula would be

Mid(FIELD,Index(FIELD,'/')-2,2)

[shiveshsingh]

Right(SubField(Cell, '/', -2), 2) as Cell

[swuehl]

And if you want to use the Subfield() function and you are looking for the first slash and your values may show more than one slash, I would use this:

Right(SubField(FIELD,'/',1),2)

[Anonymous]

Thank you very much all

[shiveshsingh]

Please close the thread