topic Re: First two characters on the left in QlikView

First two characters on the left

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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

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

Re: First two characters on the left

sunny_talwar — Mon, 12 Mar 2018 16:53:41 GMT

May be this

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

Re: First two characters on the left

sunny_talwar — Mon, 12 Mar 2018 16:55:43 GMT

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

];

Re: First two characters on the left

vishalgoud — Mon, 12 Mar 2018 17:08:17 GMT

Hi , try the below

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

Re: First two characters on the left

swuehl — Mon, 12 Mar 2018 17:51:51 GMT

The direct translation of the Excel formula would be

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

Re: First two characters on the left

shiveshsingh — Mon, 12 Mar 2018 17:54:08 GMT

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

Re: First two characters on the left

swuehl — Mon, 12 Mar 2018 17:55:15 GMT

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)

Re: First two characters on the left

Anonymous — Wed, 14 Mar 2018 11:48:58 GMT

Thank you very much all

Re: First two characters on the left

shiveshsingh — Wed, 14 Mar 2018 16:01:29 GMT

Please close the thread