I have four records of following fields.

field1 field2 field3 field4
------ ------ ----- ------
01234 70101 AAA 3
01234 70102 BBB 7
01234 70103 AAA 2
01234 70104 AAA 5

Now in the output I need

field1 field2 field3 field4
------ ------ ------ -----
01234 70102 BBB 7
01234 70104 AAA 5

For records where field1 and field3 are equal, one record is selected out of them
based on the value which is greater in field4.

In the above example for 1st, 3rd and 4th record field1 and field 3 are equal.
Now 1st record and 3rd record are compared and the one with greater value of field 4
is selected out of them, then that selected record is again compared with 4th record
and the one with greater value of field4 is selected and printed.
In this case the 4th record is hence selected.

So I want to know how to do this comparison of records within same field.

Manash,

Look for documentation regarding MAX.

Tom

Manash

For this example, you could use

TABLE FILE Fn
WRITE     FIELD1  
          FIELD2 
          FIELD3 
      MAX.FIELD4
BY        FIELD1 NOPRINT
BY        FIELD3 NOPRINT
END

If this does not work, please provide better example data.

(Sorry Tom, our responses overlapped)

This message has been edited. Last edited by: Alan B,

Manash,

Assuming FIELD2 and FIELD4 will always have the highest values in the same record, you can use the MAX. prefix on FIELD2 and on FIELD4.

TABLE FILE YOURDATA
SUM FIELD1 MAX.FIELD2 FIELD3 MAX.FIELD4
BY FIELD1 NOPRINT
BY FIELD3 NOPRINT
END

If this is NOT the case, as is apparent by the sample data you supplied, you can try something like the following. I created a report that calculates the PROFIT = RCOST - DCOST. The most profitable car is not necessarily the most expensive car (RCOST). run the following request and see that the green lines are most profitable but the blue lines are the most expensive. Add the WHERE TOTAL statement to the request to get the results you are looking for.

DEFINE FILE CAR
PROFIT/D12.2=RCOST-DCOST;
END
TABLE FILE CAR
PRINT RCOST MAX.RCOST WITHIN CAR PROFIT MAX.PROFIT WITHIN CAR
COMPUTE EXPFLAG/I1=IF (C1 EQ C2) THEN 1 ELSE 0;
COMPUTE PROFLAG/I1=IF (C3 EQ C4) THEN 1 ELSE 0;
BY COUNTRY
BY CAR
WHERE COUNTRY EQ 'W GERMANY'
-*WHERE TOTAL PROFLAG EQ 1
ON TABLE SET STYLE *
TYPE=DATA, BACKCOLOR=LIME, WHEN=PROFLAG EQ 1,$
TYPE=DATA, BACKCOLOR=CYAN, WHEN=EXPFLAG EQ 1,$
ENDSTYLE
END

Manash,

You could always use this simpler approach if there is never more than one record with the HIGHEST FIELD4 value. If you have two or more records with the HIGHEST value they will all show on the report using the following techinique. ALFA ROMEO has a tie between two reocrds.

DEFINE FILE CAR
PROFIT/D12.2=RCOST-DCOST;
END
TABLE FILE CAR
PRINT MODEL BODYTYPE RCOST PROFIT
BY COUNTRY
BY CAR
BY HIGHEST 1 PROFIT
-*WHERE COUNTRY EQ 'W GERMANY'
END

Thanx for your suggestions.
If I want to compare a record with the next record and out of these select one based on some field condition, then again compare this selected record with the next record and so on.
Is there any function or simple way to do that?

Manash,

The concept of looking at data in the next record is not achievable. The same effect though of comparing current record to previous record can be acheived using the LAST function:

  
N_RCOST/D12.2 = IF LAST RCOST GT RCOST THEN LAST RCOST ELSE RCOST;

OR

  
N_SALES/D12.2 = IF LAST MODEL EQ MODEL AND 
                   LAST CAR EQ CAR AND 
                   LAST COUNTRY EQ COUNTRY 
                   THEN LAST N_SALES + SALES ELSE SALES;

ETC.

Manash,

I think Alan is right here. The only way you're going to achieve this , is by using the LAST function. However be aware of the following rule.
The effect of LAST depends on whether it appears in a DEFINE or COMPUTE command:
In a DEFINE command, the LAST value applies to the previous record retrieved from the data source before sorting takes place.
In a COMPUTE command, the LAST value applies to the record in the previous line of the internal matrix.
So keep this in mind;

Have fun,

Thanx JJI and Alan.
I am having a tought time solving this.
if we have

field1 field2 field3 Date
------ ----- ------- -----
01234 AAA x 2/3/06
01234 AAA x 3/3/06
01234 AAA y 12/20/06
01234 BBB z 12/21/06

I have to select records based in following matrix-


FIELD3 x Y Z
---------------------------------------------------------------------------
x greater of dates x z


y x greater of dates y


z z y greater of dates



Now I need to print records with distinct field2 and for this i need to compare records
with same field2.,
Now for 1st record and 2nd record field2 is same then i need to check for field3 corresponding to the values in
matrix
So i need to compare 1st record and 2nd record.
In this case field3 is same for both records, now according to the matrix i need to select the
record with greater of the date field.
hence 2nd record should be selected.
Now again i need to compare this selected record with 3rd record as both 2nd record and 3rd record
have same field2.
In this case field3 is different for both, from the matrix i find that i need to select the
record with field3=x.
Hence i select 2nd record
Now for 4th record field2 is different so i do need to print that record also
hence in my output i want

field1 field2 field3 Date
------ ----- ------- -----
01234 AAA x 3/3/06
01234 BBB z 12/21/06
Any help how to solve it.

This is a basic approach I would try. I must admit to not totally understanding the requirements, the text seems to differ from the matrix, so i don't think this will work straight off.

  
TABLE FILE Fn
BY FLD1
BY FLD2
BY FLD3
BY DATE
ON TABLE HOLD
END
DEFINE FILE HOLD
NEW_DATE/MDY=IF LAST NEW_DATE EQ 0 THEN DATE ELSE 
                IF FLD2 EQ LAST FLD2 AND FLD3 NE LAST FLD3 THEN LAST DATE ELSE DATE;
NEW_FLD3/A1=IF LAST NEW_FLD3 EQ ' ' THEN FLD3 ELSE 
                IF FLD2 EQ LAST FLD2 AND FLD3 NE LAST FLD3 THEN LAST FLD3 ELSE FLD3;
END
TABLE FILE HOLD
WRITE MAX.FLD1
          FLD2
      NEW_FLD3
      NEW_DATE
BY ID2 NOPRINT
BY NEW_FLD3 NOPRINT
END

Manash,

I don't know if I understand your matrix. Can you please explain it a bit more. For example what does this mean: "X greater of dates XZ" ? Does it mean that you should select the X-record that contains the most recent date if you have to compare x and z records?

field3 x y z
-------------------------------------
x # x z
y x # y
z z y #

Where # is 'GREATER OF THE DATES'

sorry by mistake I sent it many times
the matrix is as follows
field3 x y z
-------------------------------------
x # x z
y x # y
z z y #

Where # is 'GREATER OF THE TWO DATES"
It means if both 1st record and 2nd record has x then i have to pick record with recent date,
if 1st record has field3 as x and 2nd record has y the i will pick record with field3 =x.
if 1st record has field3 as x and 2nd record has z the i will pick record with field3 =z.
if 1st record has field3 as y and 2nd record has x the i will pick record with field3 =x.
if both 1st record and 2nd record has y then i have to pick record with recent date.
if 1st record has field3 as y and 2nd record has z the i will pick record with field3 =y
if 1st record has field3 as z and 2nd record has x the i will pick record with field3 =z.
if 1st record has field3 as z and 2nd record has y the i will pick record with field3 =y.
if both 1st record and 2nd record has z then i have to pick record with recent date..

FIELD3 X Y Z
-------------------------------------
X # x z
Y x # y
Z z y #

I hope this makes it more clear to understand

Manash,

Using the same principle, still not convinced this will work, but seems to match your criteria (this assumes data is coming in in FLD1/FLD2 order):

  
DEFINE FILE Fn
NEW_FLD3/A1=IF LAST NEW_FLD3 EQ ' ' THEN FLD3 ELSE 
                IF FLD2 NE LAST FLD2 THEN FLD3 ELSE 
                IF FLD3 EQ 'y' AND LAST FLD3 EQ 'x' THEN LAST NEW_FLD3 ELSE 
                IF FLD3 EQ 'z' AND LAST FLD3 EQ 'x' THEN FLD3 ELSE 
                IF FLD3 EQ 'x' AND LAST FLD3 EQ 'y' THEN FLD3 ELSE 
                IF FLD3 EQ 'z' AND LAST FLD3 EQ 'y' THEN FLD3 ELSE 
                IF FLD3 EQ 'x' AND LAST FLD3 EQ 'z' THEN LAST NEW_FLD3 ELSE FLD3 ; 

NEW_DATE/MDY=IF LAST NEW_DATE EQ 0 THEN DATE ELSE 
                IF FLD2 NE LAST FLD2 THEN DATE ELSE
                IF FLD3 EQ LAST NEW_FLD3 AND DATE LT LAST DATE THEN LAST NEW_DATE ELSE                 
                IF FLD3 EQ 'y' AND LAST FLD3 EQ 'x' THEN LAST NEW_DATE ELSE 
                IF FLD3 EQ 'z' AND LAST FLD3 EQ 'x' THEN DATE ELSE 
                IF FLD3 EQ 'x' AND LAST FLD3 EQ 'y' THEN DATE ELSE 
                IF FLD3 EQ 'z' AND LAST FLD3 EQ 'y' THEN DATE ELSE 
                IF FLD3 EQ 'x' AND LAST FLD3 EQ 'z' THEN LAST NEW_DATE ELSE DATE ; 
END
TABLE FILE Fn
WRITE MAX.FLD1
          FLD2
      NEW_FLD3
      NEW_DATE
BY FLD2 NOPRINT
BY NEW_FLD3 NOPRINT
END

This message has been edited. Last edited by: Alan B,

Manash,

Thanks for the explanation. I can see it clearly now. It seems Alan already found a solution for you. I will try to test Alan's code myself (if I have the time).

Alan, I let you know if I find anything .

Dirk

The defines can be reduced considerably, but I have left the code to show how the logic works, as I would be surprised if this worked straight from the box for what Manash wanted. This type of logic has the ability to bite you.