Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
swuehl
MVP
MVP

How to subtract string from a string, subfield-wise

Hello all,

I've got two strings:

Let A = 'A, B, C, D, E';

Let B = 'C, E';

I want to get the difference / missing substrings when comparing B with A, i.e. result = 'A, B, D'

B is always is subgroup of A, delimiter is ', ' in this example but could be set as wanted.

In real application, I build the strings dynamically in the frontend, the solution also needs to be one single expression (all expressions are within a chart object with dimensions and need to regard the dimensions, but first start with a simple example).

So, anyone has a nice solution to this already?

Thanks in advance,

Stefan

1 Solution

Accepted Solutions
swuehl
MVP
MVP
Author

Hi all,

thanks everyone for your input!

I had a long night and think I managed now to get what I want.

I finally got something like

=concat( if(wildmatch(vB,'*'&subfield(vA,', ',ValueLoop(1,nAnum,1))&'*'),NULL(),subfield(vA,', ',ValueLoop(1,nAnum,1)) ) ,', ')

I attach my qvw solution for your reference.

Regards,

Stefan

View solution in original post

13 Replies
matt_crowther
Luminary Alumni
Luminary Alumni

Stefan,

This works for me: purgechar(vA,vB)

Using 2 variables 'vA' = ABCDE and 'vB' = CE

The result is ABD

All the best,

Matt - Visual Analytics Ltd

Not applicable

Stefan you can try something like this:

replace(purgechar( A,purgechar(B,',')),',,',',')

Regards,

Kiran.

swuehl
MVP
MVP
Author

Matt, thanks a lot for your quick answer, I was hoping for something simple like purgechar (which I haven't though of).

Unfortunetely, this will not work, I guess my above sample is a little simple.

Hm, maybe consider this:

Let A = '100, 200, 300, 400, 500';

Let B = '200, 500';

=purgechar('100, 200, 300, 400, 500','200, 500')

will lead to 134, which is not what I want (ok, you might say,it will indicate the right numbers, like separate the digits and multiply by 100, but then look at something like

=purgechar('122, 243, 321, 451, 521','243, 521')

which results in empty string, while I expect 122, 321, 451

I assume one needs to split at least one of the strings by its separator and then match / replace within the other. But I am open to any suggestions (still only in the frontend).

Ah, probably a macro might do?

swuehl
MVP
MVP
Author

Kiran, thanks,  too!

I think your solution has the same limitiations like Matt's regarding my hopefully now clarified requirements.

I don't think I can call a macro from within a chart expression context, so my last idea probably won't do neither.

Not applicable

Stefan,

A bit lengthy way but can you check the attachement?

Kiran.

swuehl
MVP
MVP
Author

Kiran, thank you again.

I think your solution is not much different from your above posted expression, except that you take care of comma separators in front or at the end of the resulting string, right?

Please check my above post, I hope you will see that I am looking for something a bit different (I don't think just purging char from my string will do, since I want some substrings removed, while others with partly same characters should be preserved.

But thanks again.

Not applicable

Stefan, would you please share when you find a front-end solution for this.

thanks!

swuehl
MVP
MVP
Author

Hi all,

thanks everyone for your input!

I had a long night and think I managed now to get what I want.

I finally got something like

=concat( if(wildmatch(vB,'*'&subfield(vA,', ',ValueLoop(1,nAnum,1))&'*'),NULL(),subfield(vA,', ',ValueLoop(1,nAnum,1)) ) ,', ')

I attach my qvw solution for your reference.

Regards,

Stefan

Jason_Michaelides
Luminary Alumni
Luminary Alumni

Hi Stefan,

I have never used ValueLoop() before and found this post really useful - thanks!  Hope you don't mind the hijack, but I thought I'd offer a slightly different solution for those stumbling across this thread and who don't build the strings in the front end like you do...

I generally favour longer script execution times that reduce server load during user interactions so if I can possibly do something in the script I will do so.  If the strings and substrings are pre-known and not based on user-interaction then a combination of SubField() and Exists() will achieve the same result.

Hope it helps someone - it was a useful exercise for me to pick apart your ValueLoop() solution and build a script alternative.

Love this forum

Jason