Skip to main content
cancel
Showing results for
Search instead for
Did you mean:
Specialist

## String Aggregation Function for matching text

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

1 Solution

Accepted Solutions
MVP

my attempt in the attachment

12 Replies

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

Specialist
Author

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

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

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

- Marcus

Specialist
Author

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

Creator III

Hi Richard,

Try using Pick and Wild Match

Regards,

Varun

Creator III

Try This,

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

Hope this helps !

Regards,

Varun

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

Specialist
Author

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.

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.

Community Browser