12 Replies Latest reply: Aug 25, 2015 4:07 AM by Richard Chilvers

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

• Re: String Aggregation Function for matching text

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

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

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

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

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

Hi Richard,

Try using Pick and Wild Match

Regards,

Varun

• Re: String Aggregation Function for matching text

Try This,

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

Hope this helps !

Regards,

Varun

• Re: String Aggregation Function for matching text

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

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

my attempt in the attachment

• Re: String Aggregation Function for matching text

Hi Massimo

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

Regards

• Re: String Aggregation Function for matching text

Try like below:

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