Formula for Grouping Similar Text Strings

[crogers111]

CR XI
SQL

I'm trying to create a formula that looks at the text of a string (from left to right) and groups text that is the same together. My goal is to group my report based on this formula to perform summaries.

EXAMPLE of CURRENT DATA & GROUPING:

Allergy Injections-EG
Alllery Injections-PPO
Alllery Injections-NonPPO
Chiro-EG
Chiro-PPO
Chiro-NonPPO

DESIRED DATA GROUPING:

Allergy Injections
Chiro

i.e. I'd like records with the first 3 descriptions to be grouped together as 'Allergy Injections'

 

[CoSpringsGuy]

create this formula then use it for the grouping

if {datafield} like "Allergy Injections" then "Allergy Injections"
else if {datafield} like "Chiro" then "Chiro"

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[crogers111]

Thanks for the reply.

my current data example inlcuded only 2 groupings. Unfortunately There are dozens that follow the same pattern.

I am hoping there's a formula that can acomplish this without having to list each example within the formula.

 

[CoSpringsGuy]

you could try grouping on this formula butit will depend on the first 7 characters being unique to your grouping desire.

left({datafield},7)

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[crogers111]

What I need is a formula that checks for the existence of a hyphen.

and if the string has a hyphen, then I need to capture all of the text to the left of the right most hyphen (there could be 2 hyphens), with spaces trimmed.

 

[CoSpringsGuy]

as with everything I post there is probably an easier way but try grouping on this formula:

numbervar x;
stringvar output := {yourfield};
if instr({@yourfield},"-") > 0 then
output := split({@yourfield},"-")[1];
For x := 2 To ubound(split({@yourfield},"-"))-1
Do
output := output & "-" & split({@yourfield},"-")[x];
output

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[crogers111]

This does what I need.
Thanks, much appreciated.

 

[lbass]

You could group on this formula:

if instr({table.field},"-") <> 0 then
left({table.field},instr({table.field},"-")-1)

-LB

 

[CoSpringsGuy]

I had initially thought to suggest a very similar formula but the post said there could be more than one - and they need everything to the left of the rightmost hyphen.

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[lbass]

Sorry, I missed that and didn't understand why you were proposing so complex a solution. I wrongly assumed the possible variations would be noted in the first post. Anyway, the following would also work:

if instr({table.field},"-") <> 0 then
left({table.field},instrrev({table.field},"-")-1) else
{table.field}

-LB