Forum Discussion
Advocate IDax - Keyword/Category replacement.
Hi CrownWorker -
I believe you are getting the error on the second set because you need to ensure that your keyword list has a unique set of values. i.e. you can't have the same keyword listed in your keyword table more than once.
The formula I gave you above returns the category in alphabetical order. So if there are multiple matches, the item that comes first alphabetically is what appears in the category formula.
Sounds like you want to override this alphabetic sorting and return the category by your own ranking - where you define the order of categories to return in the case of multiple matches. Here's how:
Step 1: Add a column to your keyword table and rank your keyword table like so:
Now change your matchfound formula to something resembling the following:
rankedCategory = LOOKUPVALUE( 'Keyword Table'[Narrative Contains],'Keyword Table'[Rank], CALCULATE( FIRSTNONBLANK('Keyword Table'[Rank],1), filter(
VALUES('Keyword Table'[Narrative Contains]), SEARCH('Keyword Table'[Narrative Contains],'Transaction list'[Narrative],1,0)<>0 ) ) )Which should now create the following:
This should allow you to assign a ranking in order to dictate which categories should be returned in the case of multiple matches.
Advocate IThank you for the attempt, the only problem with your solution is that it needs an exact match and I am looking for partial/similar match
Hi CrownWorker
I had a similar problem and just figured it out. Here's what I did:
I had a transaction table with various narratives. I wanted to search each description narrative to see if it contains a keyword from a keyword list If it did, return the corresponding category paired with the keyword. So like this:
Transaction list:
Keyword List:
The first thing I did was set up a column to determine if there's a match, and if so - what keyword identified the match. I created a calculated column with this formula:
MatchFound =
FIRSTNONBLANK(
FILTER(
values('Keyword Table'[Narrative Contains]),
search('Keyword Table'[Narrative Contains],'Transaction list'[Narrative],1,0))
,1)
Which adds this column to the table - that identifies the keyword that was found in the narrative string.
I then used a lookupvalue formula - to return the corresponding category like so:
category = LOOKUPVALUE( 'Keyword Table'[Category],'Keyword Table'[Narrative Contains],'Transaction list'[MatchFound] )
Which adds this category column. Voila!
Hope that helps!
- CrownWorker
The first part worked, I would like to understand how it has ordered my list of keywords so that I can order them properly to capture the information in the best possible way to represent the service.
The second part isnt working properly though giving the error indicated in the graphic below
Hi CrownWorker -
I believe you are getting the error on the second set because you need to ensure that your keyword list has a unique set of values. i.e. you can't have the same keyword listed in your keyword table more than once.
The formula I gave you above returns the category in alphabetical order. So if there are multiple matches, the item that comes first alphabetically is what appears in the category formula.
Sounds like you want to override this alphabetic sorting and return the category by your own ranking - where you define the order of categories to return in the case of multiple matches. Here's how:
Step 1: Add a column to your keyword table and rank your keyword table like so:
Now change your matchfound formula to something resembling the following:
rankedCategory = LOOKUPVALUE( 'Keyword Table'[Narrative Contains],'Keyword Table'[Rank], CALCULATE( FIRSTNONBLANK('Keyword Table'[Rank],1), filter(
VALUES('Keyword Table'[Narrative Contains]), SEARCH('Keyword Table'[Narrative Contains],'Transaction list'[Narrative],1,0)<>0 ) ) )Which should now create the following:
This should allow you to assign a ranking in order to dictate which categories should be returned in the case of multiple matches.
- CrownWorker
Hey Robbieinoz,
You are amazing. That solved every issue I had. I wish there was a way I could show my appreciation better then a few lines of text on a screen. I'll try to send some goodwill and karma your way.
Thanx again.