Want to Display (Partially) Duplicate Records

[Former Member]

I have a query created in Crystal Reports 9 that returns over 6000 records that like this:

16022-1_9 EM 50 off 100 Winter Clear

16022-1_12 EM 50 off 100 Winter Clear

16030-7_22 SA Mattress Onsert

As you can see from this example, in some cases there may be instances where the set of digits before the first dash are duplicates:

16022-1_9 EM 50 off 100 Winter Clear

16022-1_12 EM 50 off 100 Winter Clear

16030-7_22 SA Mattress Onsert

I want to be able to create a report that filters out the 6000 records and only lists those records that start with same digits before the first dash.

In the past I have used a formula in the Selection Expert like this to find duplicates: previous({FIELD}) <> {FIELD} AND next({FIELD}) <> {FIELD}  but in this case the entire field is not going to be identical, just the numbers before the dash. How do I accomplish the filtering so that I can find the records that start with the same number before the dash?

I am thinking that I need to have a formula that includes {EVENTS.eventname} startswith  - but do not know how include a regular expression for all digits before a dash.

Thanks in advance for any ideas on this.

[DellSC · Accepted solution]

Create a formula that gets the digits before the dash.  The easiest way to do this is to "split" the data into an array and take the first element.  So, it would look like this:

{@FirstNums}

Split({MyTable.MyField}, '-')[1]

I don't remember whether Crystal 9 has the option for a "Group Selection" formula (drop-down arrow by the "Select Expert" button).  If it does, you could also get rid of the section formula you're using, group by the formula above and put something like this in the Group Select:

DistinctCount({MyTable.MyField}, {@FirstNums}) > 1

This will filter out all of the data where there's only one row for that first set of digits.

-Dell

[Former Member · Accepted solution]

Thank you for the help your advice worked - I did use the Group Selection formula as you advised and got a nice clean report. I was looking for a way to use a regular expression, but after I posted I learned that Crystal does not support regex. I really appreciate the help.

Thanks again.