Forum Discussion
help needed in fuzzy mapping
I have mapping table as below Campaign Objective Campaign Code App Installs APPIN App re-engagements APPRE Awareness AWA Calibration CALIB Catalogue Sales CATSA Consideration CONSI Conv...
Greg_Deckler I saw the code but I am not able to understand how it works. If you can let me know which column i need to replace with the column that I have, I think that will be helpful. So that I can update your column with the columns that i have and check the result.
Please help
Greg_Deckler I have updated the code as below but i am not getting expected output so looking into it.
Also I feel that we need to create the calculated column instead of measure since I need matched "Campaign Objective" value based for all the rows.
Fuzzy =
VAR __MatchWord = MAX(dim_campaign_pca[campainObjective])
VAR __CleanMatchThreshold = 4
VAR __KillThreshold = 3
VAR __FuzzyThreshold1 = .4
VAR __FuzzyThreshold2 = .8
VAR __WordSearchTable =
GENERATE(
'Fuzzy Mapping',
VAR __Word = MAXX(FILTER('Fuzzy Mapping',[Campaign Code]=EARLIER('Fuzzy Mapping'[Campaign Code])),[Campaign Objective])
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