Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

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

Re: How to subtract string from a string, subfield-wise

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

12 Replies
matt_crowther
Valued Contributor

How to subtract string from a string, subfield-wise

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

How to subtract string from a string, subfield-wise

Stefan you can try something like this:

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

Regards,

Kiran.

MVP
MVP

How to subtract string from a string, subfield-wise

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?

MVP
MVP

How to subtract string from a string, subfield-wise

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

Re: How to subtract string from a string, subfield-wise

Stefan,

A bit lengthy way but can you check the attachement?

Kiran.

MVP
MVP

How to subtract string from a string, subfield-wise

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

How to subtract string from a string, subfield-wise

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

thanks!

MVP
MVP

Re: How to subtract string from a string, subfield-wise

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_michaelid
Honored Contributor II

Re: How to subtract string from a string, subfield-wise

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

Community Browser