Find Replace on first occurance

[stefaniadurdan]

Hello!

I am trying to use Find Replace tool to extract a Category from a mapping file.

The original database contains free text input by the user - it can be different each time.

Based on keywords, I am creating a mapping and I have a difficult time when they are interfering and there is a priority in place.

This is a sample of the database.

Text

Dental feb 23

Dental accrual dec 12

Accrual docs 3452

And this is a sample of the Mapping:

Group	Text	Priority
Accrual / Reversal	accrual	1
Allocate/Record Dental	dental	2

Basically, what I want to highlight is that if the free text contains "Dental" it will go under category Allocate/Record Dental, BUT if it also contains "Accrual" it will go under category Accrual / Reversal. That's why I thought to insert a Priority and sort them based on the Priority.

My logic says that when running the workflow, it should work. However, Alteryx is still mapping the text "Dental accrual dec 12" under Allocate/Record Dental instead of Accrual/Reversal.

Attached is the workflow that I tried.

This is what Alteryx is bringing me:

Text	Group
Dental feb 23	Allocate/Record Dental
Dental accrual dec 12	Allocate/Record Dental
Accrual docs 3452	Accrual / Reversal

And this is what I want:

Text	Group
Dental feb 23	Allocate/Record Dental
Dental accrual dec 12	Accrual / Reversal
Accrual docs 3452	Accrual / Reversal

Is there a solution here?

Thank you so much!

[BS_THE_ANALYST]

@stefaniadurdan I think this could be one approach:

Append all possible matches against the data. See if any of the rows contain the possible matches using "contains" formula. Find the lowest priority. Extract that row.

Not sure if this is the best route; first thing that came to mind.

All the best,

BS

[TimN]

How about this...

[caltang]

Like so?

[caltang]

Props to you @stefaniadurdan - you are very clear with your request and you provided your workflow. 

Great work @TimN @BS_THE_ANALYST as well - many ways to solve this!

[BS_THE_ANALYST]

@caltang nice, split-to-rows and join. Your approach trumps mine as the lookup tables grow! I'd be eager to use this @stefaniadurdan.