Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Employee
Employee

How to Replace Substring Based on Wildcards

I would like to replace a sub string based on a wildcard match. How would I do this? The function "Replace" does not seem to accept wildcards, but conceptually I would like to be able to do something like this:

Replace(MyField, 'b.d', 'XYZ'))

Then examples of field values and results results would look like this:

Field ValueResult
abcdeaXYZe
abfdeaXYZe
abdeabde
axcdeaxcde
9 Replies
Partner
Partner

Re: How to Replace Substring Based on Wildcards

so only bd get replaced when there is something in the middle of it?

I think you will need to use the substring/index function and concatenate the XYZ in the middle of it deleting bd when there is something between bd

Employee
Employee

Re: How to Replace Substring Based on Wildcards

OK, but how would I actually do that? It doesn't seem like the index function accepts wildcards either.

MVP & Luminary
MVP & Luminary

Re: How to Replace Substring Based on Wildcards

I think you could use mapsubstring() for it. Here two examples what is meant:

Re: Passing parameter strings that contain special characters

Re: Wildcard Lookup between two tables

- Marcus

MVP
MVP

Re: How to Replace Substring Based on Wildcards

You can use a macro to find and replace using regular expressions:

Regular expression [A-Z] *

How to use regular expressions

undergrinder
Valued Contributor II

Re: How to Replace Substring Based on Wildcards

Yes, it is a bit bloat but works:

replacemap:

Mapping

load * Inline

[

ori,new

bad,b@d

bbd,b@d

bcd,b@d

bfd,b@d

bxd,b@d

](delimiter is ',');


base:

Load * inline

[

Field

abcde

abfde

abde

axcde

eafgbxxdjk

eafgbxdjk

] (delimiter is '|');


Final:

Load

Field,

    MapSubString('replacemap',Field) as replace_step,

    replace(MapSubString('replacemap',Field),'@','NewString') as Final

Resident base;


drop table base;



G.

undergrinder
Valued Contributor II

Re: How to Replace Substring Based on Wildcards

Hi  Stefan,

It is wonderful. I didn't know about there is such opportunity to use RegExp in Sense.

G.

MVP
MVP

Re: How to Replace Substring Based on Wildcards

Well, I missed the QS part of the question, the link is related to QV. Need to check for QS.

MVP
MVP

Re: How to Replace Substring Based on Wildcards

undergrinder
Valued Contributor II

Re: How to Replace Substring Based on Wildcards

So I know there is an opportunty to write subroutine in Sense, but you can't do the same with functions.

The editor returns with 'Unknown statement: Function'