Matching pattern list with wildcard to another table

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 servicestopped	class B
app1stop	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 servicestopped	class B
app1 has stopped	app1stop	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!

Workflow

Accepted answers

AngelosPachis

Ah I see @Aldric , probably something gets lost in the summarize tool.

Maybe try grouping on the new fields (ID, team) as well? There is always the solution of using a filter tool to only keep those that the pattern matches and process those that you can't find a match separately.

You can achieve that by using a filter tool after the formula tool where the regex_match formula is found

All comments

AngelosPachis

Hi @Aldric ,

Instead of using the Find & Replace tool, maybe you can use Regex as an alternative. In the attached workflow, I have used an append fields tool to create all combinations of descriptions/patterns. Then, with a formula tool, I have replaced all asterisks to ".*?" which in Regex can be translated as everything (so something exists there, but it's not greedy, so it may also not exist).

The you can check if there is a match in the description to the pattern you have defined, with Regex Match.

Hope that helps, let me know if that worked for you

Cheers,

Angelos

example.yxmd

Tyro_abc

Please try this formula with Regular expression

if REGEX_Match([Description],'.*activate access.*' )
then 'Class A'

elseif  REGEX_Match([Description],'.*event log service.*stopped' )

then 'Class B'

elseif  REGEX_Match([Description],'.*app1.*stop.*' )

then 'Class C'

else 'n/a' endif

Regular Expression.yxmd

Aldric

thanks AngelosPachis!

your idea seems working with the subset of dummy data, but using the full set of patterns and descriptions something gets lost.

I have ~300 patterns and +10K list of descriptions, which some of them can also be the same, but having different ID - which is unique, in my sample data I didn't report all the columns that I have for the descriptions, it is something like:

description	ID	team	date	...
the user has activate access to xyz	INC123	group1	..	..
the event log service was stopped	INC456	group2	..	..
app1 has suddenly stopped	INC789	group3	...	..
the event log service was restarted	INC012	group1	..	..
app1 has suddenly stopped	INC345	group3
..

I guess that point where I get confused is when I have the same description, but I I think we are on the right way. Let me try to play a bit more.

Aldric

Thanks Tyro_abc,

this work with a small list of patterns (like 3 as in your example), but I have more than 300 patterns that can potentially change in the patterns table, I'd need something that is not using the string hardcoded in the regex.

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335