Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
richard_chilvers
Specialist
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
maxgro
MVP
MVP

my attempt in the attachment

View solution in original post

12 Replies
marcus_sommer
MVP & Luminary
MVP & Luminary

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

marcus_sommer
MVP & Luminary
MVP & Luminary

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

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

- Marcus

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

varunreddy
Creator III
Creator III

Hi Richard,

Try using Pick and Wild Match

Regards,

Varun

varunreddy
Creator III
Creator III

Try This,

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

Hope this helps !

Regards,

Varun

marcus_sommer
MVP & Luminary
MVP & Luminary

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

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.