topic Re: Replace every 3rd comma with & in QlikView

Replace every 3rd comma with &

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

Hi I want to replace every third comma in my string with '&' in a text box, can someone help me please?

Regards

Pratyush

Re: Replace every 3rd comma with &

marcus_sommer — Wed, 27 Sep 2017 14:19:03 GMT

Maybe with something like this:

mid(Field, 1, index(Field, ',', 3) - 1) & '&' & mid(Field, index(Field, ',', 3) + 1)

- Marcus

Re: Replace every 3rd comma with &

vishsaggi — Wed, 27 Sep 2017 14:22:02 GMT

Can you share how your string looks like?

Re: Replace every 3rd comma with &

prat1507 — Wed, 27 Sep 2017 14:34:35 GMT

Hi Marc

This works only for the 3rd comma, I want it on 3rd, 6th, 9th and so on.....on every 3x comma.

Regards
Pratyush

Re: Replace every 3rd comma with &

prat1507 — Wed, 27 Sep 2017 14:34:59 GMT

Take any comma separated string.

Regards

Pratyush

Re: Replace every 3rd comma with &

sunny_talwar — Wed, 27 Sep 2017 14:40:12 GMT

May be this

=Concat(Mid(Dim,

Index(Dim, ',', 3 * (ValueLoop(1, Floor(SubStringCount(Dim, ',')/3)+1)-1)) + 1,

(If(Index(Dim, ',', 3 * (ValueLoop(1, Floor(SubStringCount(Dim, ',')/3)+1))) = 0, Len(Dim), Index(Dim, ',', 3 * (ValueLoop(1, Floor(SubStringCount(Dim, ',')/3)+1)))-1)-Index(Dim, ',', 3 * (ValueLoop(1, Floor(SubStringCount(Dim, ',')/3)+1)-1))))

, '&', ValueLoop(1, Floor(SubStringCount(Dim, ',')/3)+1))

Re: Replace every 3rd comma with &

prat1507 — Wed, 27 Sep 2017 14:44:42 GMT

Thanks Sunny, worked as a miracle.

Regards

Pratyush