Eliminating duplicates in Detail Section - A Tricky one 1

[Nansat]

Problem Description

Detail section records:

Item store salesdate price price_eff_dt price_exp_dt
001 003 23-Apr-2006 $1.0 1-Oct-2005 30-Dec-2005
001 003 23-Apr-2006 $1.5 1-Apr-2006 30-Apr-2006
001 003 23-Apr-2006 $2.5 1-Apr-2006 NULL
001 003 23-Apr-2006 $1.5 1-Aug-2006 30-Sep-2006

Item, store, salesdate, price_eff_dt and price_exp_dt are Table fields.

PRICE is the crystal formula that determines the product price (based on sales date that falls in PRICE_EFF_DT and PRICE_EXP_DT) for that product and store combination.

The requirement now is, once I determine the valid price for a product and store combination (record 2 in the above example), I want to set the price as ZERO for the rest of the records for the SAME PRODUCT & STORE combination.

I tried definining global variable..etc. Nothing really worked for me so far, since its in detail section and the global variables are getting reset.

Any help will be sincerely appreciated.

Thanks, Nansat

 

[Nansat]

Thanks lbass for your continued and excellent support.

I did restore the grouping on Store and now most of the cases have been taken care except ONE case.

Records are Grouped by ITEM, SLSDATE, STORE
Records sorted by
prc_effdt -descending and
prc_expdt-descending.

Item store slsdate price prc_effdt prc_expdt Qty 001 001 23-Apr-06 $2.0 17-Apr-06 NULL 10
001 001 23-Apr-06 $1.0 10-Mar-06 03-Apr-06 10
001 001 23-Apr-06 $0.99 04-Jan-06 07-Mar-06 10
001 003 23-Apr-06 $2.7 17-Apr-06 NULL 10
001 003 23-Apr-06 $1.5 14-Mar-06 16-Apr-06 10
001 003 23-Apr-06 $0.99 04-Jan-06 13-Mar-06 10


{@enddate} {@endprice} EXPECTED-PRICE
NULL 0.0 2.0
03-Apr-06 1.0 0.0
07-Mar-06 0.0 0.0
17-Apr-06 0.0 2.7
14-Mar-06 1.5 0.0
04-Jan-06 0.0 0.0

How to get this kind of result??

 

[lbass]

Store and item should be your first and second groups (whichever one first doesn't matter), but sales date should be your group #3.

-LB