HI,
I am trying to replicate a =IF(ISNUMBER(SEARCH(Key!$T$5,$B4)) in Alteryx. Currently I have an excel file with a list of file paths. I need to extract key data then replace in appended column a response based on the key data.
I am using the Find Replace tool but it seems to be returning responses based on the order it appears in the string and not the order of the lookup table.
Example string to search:
T:\Income\2020\02) Provision\Outlook Q1\A) WNR & Subs\2020.Q1 Automated Entries.xlsx
Example look up table
SEARCH | RESULT |
Auto | Entries |
Internal Controls | Internal Controls |
Correspondence | Correspondence |
Branch | Branch Info |
R&D | R&D Credit |
Calendar | Calendar & Schedules |
Return | Return |
REIT Checklist | REIT Testing |
Provision | Provision |
The Excel answer would be "Entries" since it looks up the first entry on the table "Auto", sees if its in the string then returns the answer "Entries", if the first entry doesn't exist in the string, it moves to the second entry and so on. The order of the lookup table matters.
The Alteryx answer is "Provision". It seems to be looking at the order it appears in the string.
How do I get the Find and Replace to use the order of the lookup table, not the order of the string? Or is there a different way to do this? I have rather large tables to look up against and don't want to build these into a formula.
Thanks
Solved! Go to Solution.
@DionnaStewart
We can not access your sample data.
Can you post a part of that?
But maybe you are finding a very interesting thing.
Hi @DionnaStewart ,
I think, there is no option in the Find Replace tool to force using the order of the lookup table, but you can use the Append Fields tool, a Filter tool and a Sample tool:
I've attached a sample workflow including a second version you could use, if there are any strings to search not containing any of the search strings.
Let me know if it works for you.
Best,
Roland
This looks like it is working. Thanks for the help.