String Aggregation Function for matching text

[richard_chilvers]

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

[marcus_sommer]

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

[richard_chilvers]

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) ?

[marcus_sommer]

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

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

- Marcus

[richard_chilvers]

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".

[varunreddy]

Hi Richard,

Try using Pick and Wild Match

Regards,

Varun

[varunreddy]

Try This,

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

Hope this helps !

Regards,

Varun

[marcus_sommer]

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

[richard_chilvers]

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.

[Kushal_Chawda]

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.