Eliminate Duplicates When Replaced 1

[aelsn]

Crystal 9

I'm writing a report using a single order table. An order is placed and assigned a unique order number. That order may later be cancelled (same order number) and replaced (new order number) when something is adjusted. I now have three line items for the same order. My goal is to eliminate the line for the original and the cancel and only keep the replacement in those situations. Sounds easy enough but I'm stuck and would appreciate an assist. Here is what my data basically looks like:

Order# Qty Px Cancel Rebill Prod# B/S Comm As/Of

12345 100,000 101 99999 S $50 1/5/11
12346 250,000 100 88888 B $75 2/1/11
12346 250,000 100 1 88888 B $75 2/1/11
12347 250,000 100 1 88888 B $80 2/1/11
12348 90,000 99 99999 B $45 3/15/11
12349 80,000 102 77777 S $99 4/1/11
12349 80,000 102 1 77777 S $99 4/1/11
12350 80,000 103 1 77777 S $99 4/1/11
12351 400,000 98 88888 S $250 5/15/11

This is my desired outcome based on the above sample. . .

Order# Qty Px Cancel Rebill Prod# B/S Comm As/Of

12345 100,000 101 99999 S $50 1/5/11
12347 250,000 100 1 88888 B $80 2/1/11
12348 90,000 99 99999 B $45 3/15/11
12350 80,000 103 1 77777 S $99 4/1/11
12351 400,000 98 88888 S $250 5/15/11

Essentially, I need to get rid of the line item with a cancel indicator of "1" and also the original line item that has the same order number. I will now be left with just the line item with a rebill indicator of "1" and those records that were never cancelled.

Please advise and thank you.

 

[lbass]

What is determining the sort order? Is your date really a datetime that is being used for sorting? It appears that you want to show the most recent record, and that the datetime is being sorted in ascending order, so you could drag all fields into a group footer (group by order #) and suppress the group header and detail section.

-LB

 

[aelsn]

My date is a datetime and I can display the most recent record after grouping as you suggested. The issue is when an order is cancelled, that same order # is used on the "cancel" record and both are replaced with a new order with a new number. When I group by order number, I end up keeping the most recent record (the line item for the with the cancel indicator of "1") and the new order number it was replaced with.

Every time there is a cancel indicator of 1, I want to eliminate that line item and the original line item with the same order number. Essentially, every time an order number appears more than once I need to eliminate both instances. I can sort this data however it needs to be. Thanks again for your help.

 

[lbass]

Okay, now I see. Insert a group on Order# and then go to report->selection formula->GROUP and enter:

isnull(maximum({table.cancel},{table.order#})) or
maximum({table.cancel},{table.order#}) = 0

-LB

 

[aelsn]

Thank you. My cancel indicator field is a string and it either contains the number 1 or is blank (go figure). I could not get the group selection formula to work because "isnull" was not picking up on the blanks in that field. Apparently an empty/blank string field is not necessarily null. I see how you accomodated for this for a number field with the second line of your formula (either null or zero).

I ended up using the following as my group selection:

maximum({trd.cancel_ind},{trd.order_no}) = ""

This seems to be returning what I need as it only displays the records without a "1" in the cancel field. Either way, you solved my problem as I would never have thought to group and utilize the maximum as the group selection criteria. Thanks so much for your time.

 

[lbass]

isnull(maximum({table.cancel},{table.order#})) or
trim(maximum({table.cancel},{table.order#})) = ""

This would also check for nulls which are different from empty fields. The trim() would check for any responses with spaces
and remove them. You might not have any nulls, and would not if you have checked "convert nulls to default values" in file->report options.

-LB