Regex_match for identifying multiple keywords in 2 columns

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

Regex error.PNG

Accepted answers

apathetichell

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.

2021-06-23 (1).png

All comments

apathetichell

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

DawnDuong

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

apathetichell

2021-06-23 (1).png

Idyllic_Data_Geek

apathetichell thank you .. it did the trick

Idyllic_Data_Geek

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!

apathetichell

".*" 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...

Idyllic_Data_Geek

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

apathetichell

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.

Idyllic_Data_Geek

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

apathetichell

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:

2021-06-24 (1).png

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

Idyllic_Data_Geek

Removing the pipe does not work for me!

apathetichell

Idyllic_Data_Geek

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!

apathetichell

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.

apathetichell

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.

Idyllic_Data_Geek

PRINCETON AVE

VINCENNES RD

AVE

Idyllic_Data_Geek

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

apathetichell

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.

Idyllic_Data_Geek

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?

apathetichell

Yes. with no wildcards/regex code it is looking for an exact match. To find GAP INC you could use REGEX_Match([Field1],".*\sINC.*") which won't match princeton because it requires a space prior to INC. It will however match INCONTINENT because it will match anything after. The later match is because you could have a period, a comma, a space, or an end of line following INC

Idyllic_Data_Geek

I'm using |\sINC/s| for the latter scenario of INCONTINENT

apathetichell

that matches space INC space - exactly . You will not match "HELLO INC" OR "TESLA INC" or anything other. you need ".*\sINC" to match "TESLA INC" but I can't guarantee it will match "'TESLA INC." which is why I have "\sINC.*" - which will match "TESLA INC.","TESLA INC ","TESLA INC!" but will also match " INCAPACITATED" it's a trade off. You have to figure out are you more worried about false positives or false negatives - and only you know that.

Idyllic_Data_Geek

got it 🙂 appreciate your help so far.. Thank you! should it be |\sINC/s| or |\sINCs/| ? What is the correct syntax?

apathetichell

I'd do .*\sInc.* personally - but if you only want to match INC space on BOTH sides it's the former. later is matching "INCS|" along with whatever comes after it in your if statement.

Idyllic_Data_Geek

@apathetichell Hope that you are doing well. Can you please help me understand the role of .* infront and the end of each keyword? Also how do I specify space infront and after the keywords ...(Court[SPACE] Wildcard)

apathetichell

Hi,

so for regex the key is to actually match something. If you don't know what is showing up and where - you need to say that your expression starts with something (and ends with something) and in the middle is what you want. The "something" in this is case is any character any number of times ie .(any character) (* zero or more times).... hope that helps.

Did that regex_match for you?

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703