Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
my attempt in the attachment
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
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
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".
Hi Richard,
Try using Pick and Wild Match
Regards,
Varun
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
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.