Forum Discussion
How to extract continuous string set from a column with multiple criteria
- 5 years ago
Anonymous - OK, I updated this with a Client Name output version and Project output version in 2 additional columns. I checked the matches, don't see anything obviously amiss. Solved?
PBIX is attached.
Anonymous - OK, this might actually be the one. I added a KillThreshold that was possible because I moved the cleaning to the end to maximum the length of the match. I got rid of the "Unit" match also. So, basically once I adjusted the cleaning of the special characters and spaces at the end, I was able to increase Threshold1 so that ABB is included but Unit is excluded. The only special exception is for Blue Cross and I'm not sure how else to handle that one quite honestly. Let me know if you find any obvious issues. Updated PBIX attached.
Fuzzy Column =
VAR __MatchWord = [Client Name]
VAR __CleanMatchThreshold = 4
VAR __KillThreshold = 3
VAR __FuzzyThreshold1 = .4
VAR __FuzzyThreshold2 = .8
VAR __WordSearchTable =
GENERATE(
'Ongoing Projects',
VAR __Word = MAXX(FILTER('Ongoing Projects',[Index]=EARLIER('Ongoing Projects'[Index])),[Ongoing Projects])
RETURN ADDCOLUMNS(GENERATESERIES(3,LEN(__Word),1),"Search",LEFT(__Word,[Value]),"Original",__Word)
)
VAR __Table =
FILTER(
ADDCOLUMNS(
__WordSearchTable,
"Match",SEARCH([Search],__MatchWord,,BLANK())
),
NOT(ISBLANK([Match]))
)
VAR __Max = MAXX(__Table,[Value])
VAR __Match = MAXX(FILTER(__Table,[Value]=__Max),[Search])
VAR __Proposed =
IF(
LEN(__Match)<=__CleanMatchThreshold,
SWITCH(TRUE(),
//__Clean2 = "ABB",__Clean2,
COUNTROWS(FILTER(__Table,[Value]=__Max))>1,BLANK(),
LEN(__Match) <= __KillThreshold,BLANK(),
LEN(__Match) = LEN(__MatchWord),__Match,
LEN(__Match)/LEN(__MatchWord)>__FuzzyThreshold1 && SEARCH(__Match,__MatchWord,,0)=1,__Match,
LEN(__Match)/LEN(__MatchWord)>__FuzzyThreshold2,__Match,
BLANK()
),
SWITCH(TRUE(),
__Match = "Blue Cross" || __Match = "Blue Cross ",__Match,
LEN(__Match)/LEN(__MatchWord)<__FuzzyThreshold2 && SEARCH(__Match,__MatchWord,,0)<>1,BLANK(),
__Match
)
)
VAR __Clean1 = IF(RIGHT(__Proposed,1)="(",LEFT(__Proposed,LEN(__Proposed)-1),__Proposed)
VAR __Clean2 = IF(RIGHT(__Clean1,1)=" ",LEFT(__Clean1,LEN(__Clean1)-1),__Clean1)
RETURN
__Clean2
Greg_DecklerThank you Greg. You are truly a genius. I will go ahead and use this for my presentation today. This is really helpful. Thank you for all your time.
However I do not understand just one thing. Why was the exception given to Blue Cross?
Even National Grid has two matches with client names similar to Blue cross. While this works for National Grid, why is it not working for Blue Cross?
(See -Ernst and YoungNATIONAL Grid and Ernst and YoungBLUE Cross under client names for which blue cross exception was added). While National grip is getting mapped to YoungNATIONAL Grid, Blue Cross should get mapped to YoungBLUE Cross since the client names are of similar nature or structure. Any thoughts on why this is happening? Then may be we can figure out to eliminate giving this exception as well and have a perfectly working formula without any exceptions 🙂
Let me know you thoughts. I will go ahead and accept this as a solution post your confirmation on this piece.