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

How to subtract string from a string, subfield-wise

swuehl — Wed, 02 Nov 2011 14:32:48 GMT

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

How to subtract string from a string, subfield-wise

matt_crowther — Wed, 02 Nov 2011 15:56:51 GMT

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

How to subtract string from a string, subfield-wise

Wed, 02 Nov 2011 16:08:02 GMT

Stefan you can try something like this:

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

Regards,

Kiran.

How to subtract string from a string, subfield-wise

swuehl — Wed, 02 Nov 2011 16:09:56 GMT

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?

How to subtract string from a string, subfield-wise

swuehl — Wed, 02 Nov 2011 16:34:51 GMT

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.

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

Wed, 02 Nov 2011 16:46:20 GMT

Stefan,

A bit lengthy way but can you check the attachement?

Kiran.

How to subtract string from a string, subfield-wise

swuehl — Wed, 02 Nov 2011 16:57:38 GMT

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.

How to subtract string from a string, subfield-wise

Thu, 03 Nov 2011 20:59:27 GMT

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

thanks!

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

swuehl — Fri, 04 Nov 2011 10:40:34 GMT

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

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

Jason_Michaelides — Wed, 29 Feb 2012 13:54:01 GMT

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

How to subtract string from a string, subfield-wise

swuehl — Wed, 29 Feb 2012 15:53:28 GMT

Jason,

I agree that doing such stuff in the script might be better - if the strings and substrings are preknown, in my settings they were not, as you already noticed.

I am a little fan of the valueloop() function, since it allows to create a loop in an aggregating expression, e.g. to rebuild the mathematical summing operation using an index (summation symbol ∑ ).

I believe that is not the way the developers at QT were thinking of, but nevermind.

Thanks for sharing your solution,

Stefan

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

Thu, 27 Aug 2015 08:24:50 GMT

It helped me with another situation. Thanks for posting your solution.

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

swuehl — Thu, 27 Aug 2015 08:59:14 GMT

You're welcome.

Just out of curiosity, could you outline in which context the above solution helped you?

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

vikasmahajan — Mon, 07 Nov 2022 07:11:27 GMT

Hi Stefan,

Can you please attach Qlik Sense QVF example I need same logic to be implemented !

Thanks

Vikas