Need Selection Formula 2

[polymath5]

Hi all,
I have a field that contains business names. Some have in them LLC or L.L.O. as part of their names. Unfortunately, these have been added in various ways. So I need to Add a formula that serches in the Clients.Name field for any of:
LLC, L L C, L.L.C. L. L. C.
LLO, L L O, L.L.O. L. L. O.
ignore the case as some have been entered as Llc
These are at the end of the company name.

Thanks for any help with this!

 

[dgillz]

Instr(String1,String2} will return an integer representing where in string2 that string1 is found, or a zero if it is not found. So use this in a selection formula:

Instr("LLC",{CompanyName})>0 or
Instr("L.L.C.","CompanyName})>0 or
...
...
...

Add as many as you need to test for.
Software Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[synapsevampire]

Here's one simple way:

right(trim({MyTable.Company}),3) in 'LLC', 'LLO']
or
right(trim({MyTable.Company}),5) in ['L L O', 'L L C']

There are more elaborarte ways, but this is easily maintained and accurate. You could loop through the field and eliminate non-alpha characters and see if you construct one of these 2 patterns, but for this purpose, it seemed like overkill.

This approach will only find those on the right side of the field (end).

The instr will also give you a hit on "THE JELLO SHOOTERS COMPANY", etc.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[polymath5]

Hi Guys, Thanks for the help! I also tried using the selection expert and doing 'is like' for *llc, *llo, *l.l.c. and *l.l.o.

Is there any reason why this would not also work. It certainly seemed to cut down the found number of clients. From 45 pages to 3. I'm just wondering if I'm missing any using this method...?

Thanks again!

 

[synapsevampire]

Like the instr function, a like will return the word *HELLO* as a hit. Though with the instr you could place a space before the LLO (" LLO&quot to help with this, but then you miss the ",LLO" situation.

If your statement about it being at the end of a field is true, then the formula I supplied should be accurate.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[polymath5]

Thanks again for clearing that up. I'll give it a go.

 

[polymath5]

Well the
right(trim({MyTable.Company}),3) in 'LLC', 'LLO']
or
right(trim({MyTable.Company}),5) in ['L L O', 'L L C']
works but naturally I have one client that has a company name that ends with Amirillo !!

No big problem, but what would be the best way to eliminate him from the selection?

Thanks again!

 

[dgillz]

if right({companyname},8}="amarillo" then false else
right(trim({MyTable.Company}),3) in 'LLC', 'LLO'] or
right(trim({MyTable.Company}),5) in ['L L O', 'L L C']




Software Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[polymath5]

Thanks, that works for Amirillo.

Is there a way to do it without needing to be that spcific? I guess I'm looking for a way to eliminate any records that have any character other than a space or comma as the character to the immediate left of the 1st L.

I'm thinking of the future,where we may add clients that have similar name endings.

Thanks again!

 

[Ngolem]

'LLC', 'LLO', 'L L O', 'L L C'

ARE THESE ALWAYS UPPERCASE WHEN THEY ARE VALID???

if so we can modify the formula as follows:

(right(trim({MyTable.Company}),3) in 'LLC', 'LLO'] or
right(trim({MyTable.Company}),5) in ['L L O', 'L L C'] )
and
asc(mid(trim({MyTable.Company}),length(trim({MyTable.Company}))- 2,1)) = asc("L&quot

in all 4 senarios there is a "L" in the third character from the end.

JimBroadbent@Hotmail.com

 

[polymath5]

No, quite a few are in mixed case. Llc or Llo are the most common variations.

Thanks again for your help!

 

[synapsevampire]

You should consider adding to this formula that the character BEFORE the LLC, LLO is not an alpha, that way you'll eliminate most bad hits, all if your data entry people are 100%.

This will tell you if the preceding character is in A-Z, which you'd want to eliminate:

and

not(asc(uppercase(mid(trim({KAI_VW_CLASSIFICATION_DETAIL.SOLNODEDESCRIPTION}),length(trim({KAI_VW_CLASSIFICATION_DETAIL.SOLNODEDESCRIPTION}))- 3,1)) ) in (65 to 90))

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Ngolem]

personally...you should do database repair and tighten up the user input, since you can always dream up exceptions then. Jim Broadbent

 

[synapsevampire]

Human error is unavoidable, Ngolem, just look at my code and Typoes

Creating a separate field for the LLC and LLO might be a reasonable idea...

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[polymath5]

Thanks again for the help! I'll give it a try today.

Part of what this report is for IS to get the extentions uniform. In the current SQL db this is enforced. But over 20,000 records were converted from a previous system that didn't enforce any standard. So after the report is generated and used for it's original intention, it will be given to people to go into the application and correct the records.

Also, this application cannot be modified (can't add fields, etc), so I'm stuck having to deal with it as is.

Again thanks to all!

 

[synapsevampire]

polymath5: You can more readily correct these records (make them uniform) using an update directly using SQL.

Ask your DBA, correcting 20K records manually is bad business.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[polymath5]

Sorry, there are 20,000 records to go through, not 20,000 that were wrong.

We found only 91 that had LIc or Llc, etc. This will be no problem to manually correct.