Dynamic search and referencing

Hello,

I've 2 files - Problem_Desc and Equipment. Problem_Desc file has 2 columns - Area# and Problem_Desc which is a free text field as long as 4-10 lines. Equipment file has 2 coumns - List of Equipment# and Area#. On Problem_Desc file, I want every row in column B to be compared against column B (equipment) of equipment file and return the matching value. Meaning, cell B2 of Problem_Desc file needs to be compared against all of column B values of Equipment file and if there is a matching value, give me this value in column C of problem_desc file.

eg: Lets say cell B2 of Problem_desc file reads REFRIGERATION / Rack House / Compressor Lockout - Single / AUTOMATED WORK ORDER. Job Type: refrigeration Assigned To: 10101 Units Affected: 1 Problem Type: Compressor Lockout - Single Systems Affected: MT-D-3.Manual Comment: N/A Fault Summary: Compressor 4 Lock Out.

And one of the values of column B in equipment table reads "MT-D-3-1241". In this case there is a partial match and hence return "MT-D-3" in column C.

Note: Not all rows of Problem_Desc file will have the equipment information mentioned in column B. Only for the rows containing these equipment information, return the matching values, if not return "no match". Also, EVEN IF THERE IS A PARTIAL OR NOT A COMPLETE MATCH, STILL RETURN THE MATCHING VALUES.

Please let me know how this can be achieved.

Datasets

Fuzzy Match

Dynamic Processing

Text Mining

Data Investigation

Accepted answers

seinchyiwoo

Try attached and see if it works now?

Cheers,

Seinchyi

iterative macro - looping and find keywords.yxmd

find keyword macro.yxmc

All comments

BrandonB

Normally I would recommend using the find and replace tool for finding a string in a larger body of text and then appending on a match value if it finds something. However, your equipment list actually wouldn't match at all because it has -1241 at the end of it. However, if you took the -1241 off of the end of the equipment table, it does successfully find it in the data and returns a match

With -1241 taken off the equipment table

match 2.png

Workflow is attached as reference. You can easily find a smaller string within a larger body of text, but your smaller string needs to be the smallest acceptable match based on what you have provided.

match.yxmd

seinchyiwoo

Hi,

Fuzzy match is one approach to go but I have something similar built for another use case using iterative macro.

Don't bother about the column name as I'm just using an old workflow but put in your data for a quick test.

Based on what you've described, it seems the length of equipment name that you are able to find in the description are uncertain.

Hence, I built a process to split the equipment into a keyword column consists of various length:

Using this keyword table, I then used the iterative macro that I built a while ago to loop through the keywords table and see if I can find any match at any length and the result is below:

I suspect certain things in the iterative macro will need to be changed to work exactly on what you want but just to give you an idea on how possibly iterative macro might help you.

Hope this helps.

iterative macro - looping and find keywords.yxzp

mercurial_maverick

@BrandonB Hello Brandon, Thank you for your response. However, my list of entries necessitate me to not remove the "-1241" as there are records that have the Area# as suffix while most other records do not have this suffix. Hence I do not want to change the contents and instead create a dynamic tool that would cater to multiple instances.

mercurial_maverick

Hello @seinchyiwoo , thank you for your response. Looks like what you've here might do the trick however I'm yet to use it on my test case. I'm unable to access your workflow because of the below error. Could you please help me with an alternative way or post the formula for every step here? Also, could you please let me know the tool name you used where it says "iterative macro to loop"?

Failure to Import C:\Users\r0m011n\Downloads\iterative macro - looping and find keywords.yxzp.
There was an error opening "C:\Users\r0m011n\Downloads\iterative macro - looping and find keywords\iterative macro - looping and find keywords.yxmd":

This workflow was created by a more recent version of Alteryx, and may contain tools or functionality not present in this version. Alteryx does not support using an earlier version of Alteryx to open a workflow created with a newer version. For best results, download the latest version of Alteryx.

BrandonB

@seinchyiwoo I like that solution! Very creative way of deconstructing the search terms and allowing for a hard coded length parameter will help in the tuning of this process. @mercurial_maverick the one thing to be aware of is the threshold that needs to be set which in this case is length. I would think about the shortest length of keyword that you would like to search for, because as you could imagine, looking for a single letter could trigger a match across more of your data than you would like.

As for the version question, what version of Alteryx are you using? @seinchyiwoo may be able to change the version number in the XML for you or upload the iterative macro separately.

mercurial_maverick

Hello @BrandonB Understood, thank you for your input. I'm using 2019.4.8.22007 version.

seinchyiwoo

Try attached and see if it works now?

Cheers,

Seinchyi

iterative macro - looping and find keywords.yxmd

find keyword macro.yxmc

Quick Links

This months top contributors

LanisC 335

hhaigh1 280

erich_shiino 280

FláviaB 262

JORGE4900 235