topic Re: How to Replace Substring Based on Wildcards in App Development

How to Replace Substring Based on Wildcards

Øystein_Kolsrud — Thu, 13 Sep 2018 07:59:21 GMT

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 Value	Result
abcde	aXYZe
abfde	aXYZe
abde	abde
axcde	axcde

Re: How to Replace Substring Based on Wildcards

bramkn — Thu, 13 Sep 2018 08:19:45 GMT

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

Re: How to Replace Substring Based on Wildcards

Øystein_Kolsrud — Thu, 13 Sep 2018 10:01:08 GMT

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

Re: How to Replace Substring Based on Wildcards

marcus_sommer — Thu, 13 Sep 2018 10:19:52 GMT

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

Re: How to Replace Substring Based on Wildcards

swuehl — Thu, 13 Sep 2018 10:25:01 GMT

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

Regular expression [A-Z] *

How to use regular expressions

Re: How to Replace Substring Based on Wildcards

undergrinder — Thu, 13 Sep 2018 10:27:48 GMT

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;

Re: How to Replace Substring Based on Wildcards

undergrinder — Thu, 13 Sep 2018 10:31:02 GMT

Hi Stefan,

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

Re: How to Replace Substring Based on Wildcards

swuehl — Thu, 13 Sep 2018 10:41:33 GMT

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

Re: How to Replace Substring Based on Wildcards

swuehl — Thu, 13 Sep 2018 10:44:01 GMT

Maybe have a look at

https://help.qlik.com/en-US/connectors/Subsystems/Web_Connectors_help/Content/Connectors_QWC/Data-Source-Connectors/RegE…

Re: How to Replace Substring Based on Wildcards

undergrinder — Thu, 13 Sep 2018 10:46:42 GMT

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'