on 04-01-2016 6:10 PM
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.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
87 | |
10 | |
10 | |
10 | |
7 | |
6 | |
6 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.