Eliminating Duplicates 1

[mdcson]

I have a report with data that resembles the following:

Pat ID Code
405377353-1 22255
405377353-1 63201
405377353-1 25894
405377353-2 0762
405377353-3 0651

There are a few other fields as well to the right that are not necessary to show here and many other Pat ID's, most with only one unique number and suffix, however, a few are like this.

The numbers after the "-" are simply suffixes to show a modified bill. What I am trying to do is to get an output that only gives me the Pat ID, and all its respective data, with the latest suffix.

Thus, in this case, the only line in the output should be
405377353-3 0651

 

[lbass]

Insert a group on this formula {@ID}:

split({table.pat_ID},"-")[1]

Then go to report->selection formula->GROUP and enter:

{table.pat_ID} = maximum({table.pat_ID},{@ID})

Any calculations that you use should be based on running totals, since the more usual summaries would pick up data from non-group-selected records.

The above should work as long as the extensions don't go beyond the number 9. If they do, let me know.

-LB

 

[mdcson]

Thank you. Actually yes, there are a few occasions where the suffix is two digits.

 

[lbass]

Ok, then create a second formula {@IDwithext} like this (which assumes there is always an extension:

{@ID}+"-"+totext(val(split({table.pat_ID},"-")[2]),"00")

If an extension can be greater than 99, add another zero. Then change the group selection formula to:

{@IDwithext} = maximum({@IDwithext},{@ID})

-LB