topic Re: Regex_match for identifying multiple keywords in 2 columns in Alteryx Designer Desktop Discussions

Regex_match for identifying multiple keywords in 2 columns

Idyllic_Data_Geek — Wed, 23 Jun 2021 14:45:47 GMT

I have below calculation which was working until I created a new formula before regex function,

if REGEX_Match([Payee Name],".LLC.||.CORP.||.DEPT.||.TRUSTEE.||.ESTATE.||.CLERK.||.INC.||.DEPARTMENT.||.CONSULTING.||.DBA.||.TREASURY.||.ASSOCIATES.||.AGENCY.||.SERVICE.||.TREASURY.||.LAW.||.REALTY.||.GROUP.||.COLLECTION.")
THEN 'ENT'
elseif REGEX_Match([Payee addr 1],".LLC.||.CORP.||.DEPT.||.TRUSTEE.||.ESTATE.||.CLERK.||.INC.||.DEPARTMENT.||.CONSULTING.||.DBA.||.TREASURY.||.ASSOCIATES.||.AGENCY.||.SERVICE.||.TREASURY.||.LAW.||.REALTY.||.GROUP.||.COLLECTION.")
then 'ENT'
elseif [Payee addr 1] != '' and REGEX_CountMatches([Payee addr 1], '[0-9]') <= 0 then 'MULT'
elseif( REGEX_CountMatches([Payee addr 1], '[0-9]') <= 0 and REGEX_Match([Payee Name],".SR.")) then 'MULTI SR'
elseif( REGEX_CountMatches([Payee addr 1], '[0-9]') <= 0 and REGEX_Match([Payee addr 1],".SR.")) then 'MULTI SR'
elseif( REGEX_CountMatches([Payee addr 1], '[0-9]') <= 0 and REGEX_Match([Payee Name],".*III.*")) then 'MULTI III'
elseif( REGEX_CountMatches([Payee addr 1], '[0-9]') <= 0 and REGEX_Match([Payee addr 1],".*III.*")) then 'MULTI III'
elseif( REGEX_CountMatches([Payee addr 1], '[0-9]') > 0 and REGEX_Match([Payee Name],".JR.")) then 'IND JR'
elseif( REGEX_CountMatches([Payee addr 1], '[0-9]') > 0 and REGEX_Match([Payee Name],".SR.")) then 'IND SR'
ELSE 'IND'
ENDIF

The requirement was to identify certain type of keywords and then categorize them based on the results. Now before calling out that Regex function I created a formula which is :

if [Payee addr 1] = 'UNKNOWN' then null()
else [Payee addr 1]
endif

The result of the above is that where ever the unknown is being nulled the Regex calc is giving me unexpected result of ENT instead of IND. Results screenshot attached

Thanks in advance for your help

Re: Regex_match for identifying multiple keywords in 2 columns

apathetichell — Wed, 23 Jun 2021 14:58:43 GMT

can you post sample data? switch statements would definitely make things clearer.

Re: Regex_match for identifying multiple keywords in 2 columns

DawnDuong — Wed, 23 Jun 2021 15:05:38 GMT

Hi @Idyllic_Data_Geek

If everything else is working fine except the “else” then i suggest you explicitly call out the null test

If isnull(<fieldname>) then “IND”

elseif ... <the rest if your test conditions>
then “errors”

Endif

without knowing some sample data, that’s the best i can think of

Dawn

Re: Regex_match for identifying multiple keywords in 2 columns

apathetichell — Wed, 23 Jun 2021 15:19:40 GMT

Double pipes are your problem. Get rid of the double pipes and turn them into single pipes and it works. The single pipe is your regex "or" operator - so having a double pipe means you are accepting null() in your set of things to match for ie || effectively means OR null(). See screenshots with single pipe and one set of double pipes.

Re: Regex_match for identifying multiple keywords in 2 columns

Idyllic_Data_Geek — Wed, 23 Jun 2021 16:21:59 GMT

@apathetichell thank you .. it did the trick

Re: Regex_match for identifying multiple keywords in 2 columns

Idyllic_Data_Geek — Thu, 24 Jun 2021 13:35:06 GMT

Quesytion -- do you know what the * is doing in the below regex calc?

The Department is being flagged correctly but Departments is not.what does the period and asterisk accomplish in .*DEPARTMENT.*?

Thanks in Advance!

Re: Regex_match for identifying multiple keywords in 2 columns

apathetichell — Thu, 24 Jun 2021 13:59:42 GMT

".*" means anything any number of times so it's basically saying wildcard LLC wildcard. Each entry repeats with a wildcard before and after. note - the wildcard doesn't need to be fulfilled - it's just there as a check.

I'd have to see the full data on the Departments entry and the match to explain but keep in mind that this might not be the ideal regex use - by definition PARTS (or something) could match DEPARTMENTS prior to DEPARTMENT...

Re: Regex_match for identifying multiple keywords in 2 columns

Idyllic_Data_Geek — Thu, 24 Jun 2021 13:59:24 GMT

then with .*DEPARTMENT.*...the code should recognize Departments as well?

Re: Regex_match for identifying multiple keywords in 2 columns

apathetichell — Thu, 24 Jun 2021 14:02:24 GMT

It SHOULD. Issues are is it case insensitive and is departments matching anything else beforehand in the regex statement.

department True
departments True

is what I got from my test.

Re: Regex_match for identifying multiple keywords in 2 columns

Idyllic_Data_Geek — Thu, 24 Jun 2021 14:15:44 GMT

It's not case sensitive. Can you please test it with the Payee Name: WILHELMINA MARTIN. why is being flagged as 'ENT' and not 'IND'? Below is the full code that I have,

if REGEX_Match([Payee Name],".*LLC.*||.*CORP.*||.*DEPT.*||.*TRUSTEE.*||.*ESTATE.*||.*CLERK.*||.*INC.*||.*DEPARTMENT.*||.*CONSULTING.*||.*DBA.*||.*TREASURY.*||.*ASSOCIATES.*||.*AGENCY.*||.*SERVICE.*||.*TREASURY.*||.*LAW.*||.*REALTY.*||.*GROUP.*||.*COLLECTION.*||.*SOCIETY.*||.*STATE.*||.*DEPARTMENT.*||.*ASSOCIATION.*||.*COUNTY.*||.*CONSULATE.*||.*INSURANCE.*||.*DISTRICT.*||.*COUNCIL.*||.*PROGRAM.*||.*CLEANING.*||.*LTD.*||.*PROGR.*||.*TREASURER.*||.*RESORT.*||.*APARTMENTS.*||.*MANAGEMENT.*||.*CONDOMINIUM.*") and !ISNULL([Payee Name])
THEN 'ENT'
elseif REGEX_Match([Payee addr 1],".*LLC.*||.*CORP.*||.*DEPT.*||.*TRUSTEE.*||.*ESTATE.*||.*CLERK.*||.*INC.*||.*DEPARTMENT.*||.*CONSULTING.*||.*DBA.*||.*TREASURY.*||.*ASSOCIATES.*||.*AGENCY.*||.*SERVICE.*||.*TREASURY.*||.*LAW.*||.*REALTY.*||.*GROUP.*||.*COLLECTION.*||.*SOCIETY.*||.*STATE.*||.*DEPARTMENT.*||.*ASSOCIATION.*||.*COUNTY.*||.*CONSULATE.*||.*INSURANCE.*||.*DISTRICT.*||.*COUNCIL.*||.*PROGRAM.*||.*CLEANING.*||.*LTD.*||.*PROGR.*||.*TREASURER.*||.*APARTMENTS.*||.*RESORT.*||.*MANAGEMENT.*||.*CONDOMINIUM.*") and !ISNULL([Payee addr 1])
then 'ENT'
elseif !ISNULL([Payee addr 1]) and REGEX_CountMatches([Payee addr 1], '[0-9]') <= 0 then 'MULT'
elseif((REGEX_CountMatches([Payee addr 1], '[0-9]') > 0 or ISNULL([Payee addr 1]))and REGEX_Match([Payee Name],".SR.")) then 'MULTI SR'
elseif((REGEX_CountMatches([Payee addr 1], '[0-9]') > 0 or ISNULL([Payee addr 1])) and REGEX_Match([Payee addr 1],".SR.")) then 'MULTI SR'
elseif( REGEX_CountMatches([Payee addr 1], '[0-9]') <= 0 and REGEX_Match([Payee Name],".*III.*")) then 'MULTI III'
elseif( REGEX_CountMatches([Payee addr 1], '[0-9]') <= 0 and REGEX_Match([Payee addr 1],".*III.*")) then 'MULTI III'
elseif((REGEX_CountMatches([Payee addr 1], '[0-9]') > 0 or ISNULL([Payee addr 1]))and (REGEX_Match([Payee Name],".*JR.*"))) then 'IND JR'
elseif( REGEX_CountMatches([Payee addr 1], '[0-9]') > 0 and REGEX_Match([Payee Name],".SR.")) then 'IND SR'
ELSE 'IND'
ENDIF

Re: Regex_match for identifying multiple keywords in 2 columns

apathetichell — Thu, 24 Jun 2021 14:32:45 GMT

Match is in the payee addr not the name - I transcribed and edited your relevant if statement (got rid of double pipes) and ran this:

So it's not Payee Name which is triggering the false match - at least if you clean up the code.

Re: Regex_match for identifying multiple keywords in 2 columns

Idyllic_Data_Geek — Thu, 24 Jun 2021 14:52:25 GMT

Removing the pipe does not work for me!

Re: Regex_match for identifying multiple keywords in 2 columns

apathetichell — Thu, 24 Jun 2021 15:06:57 GMT

Re: Regex_match for identifying multiple keywords in 2 columns

Idyllic_Data_Geek — Thu, 24 Jun 2021 15:10:13 GMT

Understood. But the payee addr 1 is using the same logic and looking for the same keywords. The payee addr1 is not having any of those keywords so I'm not sure why it is flagging as ENT. I can not post the data here...but I hope that you are understanding what I'm stating!

Re: Regex_match for identifying multiple keywords in 2 columns

apathetichell — Thu, 24 Jun 2021 15:12:12 GMT

Can you post the payee addr 1 for the entry you posted? if not is it possible that these letters are found in the address?

LAW

CORP

RESORT

STATE

INC

like if the address is 170 STATE ST. that would flag, or 220 LAWTON would flag.

Re: Regex_match for identifying multiple keywords in 2 columns

apathetichell — Thu, 24 Jun 2021 15:14:24 GMT

If you can't cut and paste - I'd recommend troubleshooting on your own by cutting and pasting parts to figure out where the error is occurring but as noted - you are using regex as kind of a natural language editor and it's not quite that. I'd say the approach you are using will never be perfect especially on larger data sets so you just kind of have to figure out how you are handling and dealing with the errors.

Re: Regex_match for identifying multiple keywords in 2 columns

Idyllic_Data_Geek — Thu, 24 Jun 2021 15:14:43 GMT

PRINCETON AVE

VINCENNES RD

AVE

Re: Regex_match for identifying multiple keywords in 2 columns

Idyllic_Data_Geek — Thu, 24 Jun 2021 15:20:24 GMT

so instead of using the wildcard all over.. how can I define "(space)LLC(Space)"

Re: Regex_match for identifying multiple keywords in 2 columns

apathetichell — Thu, 24 Jun 2021 15:32:23 GMT

Yeah if you modify your code from ".*" to "\s" in all instances you should only find instances where space \keyword\ space are met. so "\sINC\s" should exclude Princeton. Again this won't help with STATE STREET.

Re: Regex_match for identifying multiple keywords in 2 columns

Idyllic_Data_Geek — Thu, 24 Jun 2021 16:03:46 GMT

got it. thank you. If I do just the |INC|, then is it looking for the values of [Field] = INC and if it has GAP INC then it will reject it?