Dynamic search and referencing

[mercurial_maverick]

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.

[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

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.

[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.

[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 · Accepted solution]

Try attached and see if it works now?

Cheers,

Seinchyi

[mercurial_maverick]

Hello @seinchyiwoo !

Yes, I'm able to access the attached. I connected both the components and played with and the logic seems to do what I intended to. Let me connect it with my actual workflow and see how it functions and get back to you on this. But yeah this is a great start and could be a potential break through. Really appreciate your efforts on this!