Matching pattern list with wildcard to another table

[Aldric]

hello, 

I'm struggling to create a table with matching from a list of string with wildcards.

I tried to follow the suggestion on this post using the Find and Replace tool, but it works only removing the wildcard * and only when the wildcard is not in the middle of my string to search. So I get only partially results.

I explain what I've been trying to achieve:

1. I have a pattern list with a related classification field in one table similar to this:

pattern	classification
*activate access*	class A
*event log service*stopped	class B
app1*stop*	class C

2. I have a long list of descriptions from another table:

description

the user has activate access to xyz

the event log service was stopped

app1 has suddenly stopped

the event log service was restarted

..

..

Searching the pattern string from the first table into each description field, I have to obtain the classifications that match that pattern (for some I might not have the pattern which is fine). So resulting table should be something like this:

description	pattern	classification
User1 need to activate access to xyz	*activate access*	class A
the event log service was stopped	*event log service*stopped	class B
app1 has stopped	app1*stop*	class C
the event log service was restarted	n/a	n/a
…
..

As said, with the find and replace tool (with removing the initial and last *) I match some pattern (like "activate access"), but where the * is in the middle I couldn't find a way to get the match that I need.

Any suggestion is appreciated!

thanks in advance!

[Tyro_abc]

Let's say we have 5 rows in original file and 4 patterns to match. After using "Append Tools" , we would get 5X4 = 20 records.

i.e. for each record, there can be 1 or more matching pattern and 1 or more  non-matching pattern. Idea is, once we get a matching pattern, we do not need to care about non-matching pattern ( for that particular line). But if we do not get any match, then we need to pick up 1 record from non-matching pattern so that we do not miss that record completely.

How many categories are you generating? We have to make sure "n/a" is last value in sorting sequence.  That would ensure we pick up the matching pattern for a particular row when we have at least one pattern matching.

[Aldric]

yes, it's completely right what you say. I had the same issue with the first workflow.

basically I have 20 records for each description, right. And among them I have 1 matching (2-3 in some case) + 19 not matching for each description. When I sort and I take the "first" description I should just get the matching one and the n/a for those that didn't have any.

This is how it should works and I fixed this in my first workflow. Just with this one I didn't get where I do loose the 55 records...

Let me retry later to go deeper, I fixed my issue, but it's good to understand what's wrong, in theory it should work too.