Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

REPLACE function

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!!

2 Replies

Re: REPLACE function

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

jsn
Honored Contributor

Re: REPLACE function

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).

Community Browser