topic Re: REPLACE function in QlikView

REPLACE function

Mon, 17 Sep 2012 10:34:31 GMT

Hi,

I was wondering if it would be possible to do the following in QV:

I have a field which contains a series of letters: e.g. ABC-12345-12345

I need to find the first "-" and replace it with a "*" - e.g. ABC*12345-12345

The first part (e.g. ABC) is variable in length. I'm trying to use the Subfield and Replace functions and I can't seem to get this to work.

Any help would be greatly appreciated.

Thanks!!

Re: REPLACE function

Miguel_Angel_Baeyens — Mon, 17 Sep 2012 10:43:32 GMT

Hi,

Try the following:

=Left('ABC-12345-12345', Index('ABC-12345-12345', '-') -1) & '*' & Mid('ABC-12345-12345', Index('ABC-12345-12345', '-') +1)

Hope that helps.

Miguel

Re: REPLACE function

Anonymous — Mon, 17 Sep 2012 10:50:17 GMT

I'm pretty sure this can be done in a nicer way but it's almost 7pm so here goes:

=replace(left('ABC-12345-12345',index('ABC-12345-12345','-')),'-','*')&right('ABC-12345-12345',len('ABC-12345-12345')-index('ABC-12345-12345','-'))

Just replace the 'ABC-12345-12345' with your field name and it should be ok.

What it does is basically to take the first section up until the first dash '-' and replaces the dash with a star '*'. Then it concatenates on the part on the right up until the first dash (which is now a star) by calculating the length of the string and subtracting the distance from the start until the first dash (star).