topic How to remove first 4 characters from the data in QlikView

How to remove first 4 characters from the data

farheenayesha — Fri, 17 Jan 2014 08:09:33 GMT

Hi,

I want to remove first 3 digits and hiphen(-) from the below data. Could anyone explain me how to do it. I tried many functions like index and all but not getting the desired result.

Re: How to remove first 4 characters from the data

jagan — Fri, 17 Jan 2014 08:36:39 GMT

Hi,

Try Mid()

=Mid('021-BioProcess', 5)

Regards,

Jagan.

Re: How to remove first 4 characters from the data

Fri, 17 Jan 2014 08:39:26 GMT

Hi,

If you need to remove the first 4 characters, you may use a function like mid():

mid('abcdefghiljk' 3) will return cdefghijk

The problem you are facing is that there is not always 4 charcaters to remove. It depends on the hyphen=> you will need to test. You will use another string function: index() that retruns an integer (0 if it does not find), FindOneOf() ....

index('abcdef' 'ef') will return 5

index('abcdef' 'x') will return 0

Now, you need to mix all that.

In the LOAD

LOAD [...]

if(index(FiledName '-')=0, FieldName, mid(Fieldname, 4)) as sth
FROM [...]

In an expression in the GUI, you could write the same as above also if your fieldname contains too many characters you want to remove.

Fabrice

Re: How to remove first 4 characters from the data

Fri, 17 Jan 2014 08:42:20 GMT

Hi Ayesha,

I think In the load you could try something like :

if(mid(Business,4)='-',mid(Business,5),Business) as Business2

That checks if 4 chacter is - and if it is then will trim out first 4 digits otherwise will leave as it to accommodate for the values that do not have numbers, like"Catch All" etc

Re: How to remove first 4 characters from the data

Fri, 17 Jan 2014 08:45:26 GMT

Hi,

You can use this expression :

PurgeChar(PurgeChar(test,TextBetween(test,'','-')),'-')

Instead of test use your field.

HTH

-Shruti

Re: How to remove first 4 characters from the data

farheenayesha — Fri, 17 Jan 2014 09:06:07 GMT

Thank you everyone for all your replies..