topic Re: String Aggregation Function for matching text in QlikView

String Aggregation Function for matching text

richard_chilvers — Mon, 24 Aug 2015 14:53:07 GMT

My data looks like:

Code, Description, Value

ABC, 20% discount , £100

ABC, 20% discount voucher, £200

ABC, 20% discount with money back, £140

DEF, another discount, £20

etc.

I have a straight table chart to aggregate (SUM) values by Code:

ABC £340

DEF £20

etc.

Is there a string function to show the first n matching characters in Description?

Eg. ABC, 20% discount, £340

Re: String Aggregation Function for matching text

marcus_sommer — Mon, 24 Aug 2015 15:05:59 GMT

You could use concat() to aggregate your string-values and then apply an mid() or left() function to cut it after x-chars or you used subfield() for returning a certain string and/or you combined it in some ways.

- Marcus

Re: String Aggregation Function for matching text

richard_chilvers — Mon, 24 Aug 2015 15:13:14 GMT

Hi Marcus

I believe CONCAT() would give me a string like:

"20% discount;20% discount voucher;20% discount with money back"

how would I then reduce this to a string "20% discount" (ie. the common left portion) ?

Re: String Aggregation Function for matching text

marcus_sommer — Mon, 24 Aug 2015 15:19:28 GMT

In this case per subfield() which used the same delimiter which you used for concat():

subfield(concat(distinct Description, ';'), ';', 1)

- Marcus

Re: String Aggregation Function for matching text

richard_chilvers — Mon, 24 Aug 2015 15:28:28 GMT

I probably didn't explain the issue well enough.

I believe your solution will CONCAT all the strings, separated by ';' and then extract the first entry up to the ';'.

So - if my string was:

"20% discount and free gift;20% discount voucher;20% discount with money back"

then this function would give "20% discount and free gift".

What I'd like is only the common string of "20% discount".

Re: String Aggregation Function for matching text

varunreddy — Mon, 24 Aug 2015 15:38:10 GMT

Hi Richard,

Try using Pick and Wild Match

Regards,

Varun

Re: String Aggregation Function for matching text

varunreddy — Mon, 24 Aug 2015 15:44:19 GMT

Try This,

if(wildmatch(Description,'*20%*'), pick(wildmatch(Description,'*20%*'),'20% Discount'))

Hope this helps !

Regards,

Varun

Re: String Aggregation Function for matching text

marcus_sommer — Mon, 24 Aug 2015 15:44:43 GMT

Then you will need some logic to identify which or how many chars from string you want to return, maybe you used index() to find the max. number of return-chars like:

left(Expression, index(Expression, ' ', 2) - 1)

- Marcus

Re: String Aggregation Function for matching text

richard_chilvers — Mon, 24 Aug 2015 15:55:34 GMT

Thanks Marcus & Varun

Neither solution will give me a solution which is flexible enough. But I will keep exploring.

What I need is a character by character compare starting from the left until a character position fails the match.

Re: String Aggregation Function for matching text

Kushal_Chawda — Mon, 24 Aug 2015 15:57:39 GMT

Hi,

You might have the other values also like 20% discount. 15 % discount... etc with many combinations. Without knowing the actual data it will be difficult to build the correct logic.

Re: String Aggregation Function for matching text

maxgro — Mon, 24 Aug 2015 17:15:30 GMT

my attempt in the attachment

Re: String Aggregation Function for matching text

Mon, 24 Aug 2015 17:19:45 GMT

Try like below:

SubField( FieldDesc , ',',1) & ' ' & SubField( FieldDesc , ',',3)

Re: String Aggregation Function for matching text

richard_chilvers — Tue, 25 Aug 2015 08:07:22 GMT

Hi Massimo

Thanks for taking the time to understand my requirement and come up with a solution

Regards