I am trying to compare values in each row of a column and based on these comparisons, toggle a flag. Using LAST funtion, I could compare a row with the previous row. But I am trying to compare a row with the following row.

By using COMPUTE, this is how I came up with col4

COMPUTE col4/D1 = IF col3 EQ 'Stg2' AND LAST col2 EQ 'Stg1' THEN 1 ELSE 0

col1 col2 col3 col4 col5
1 Stg1 0 0
2 Stg1 0 1
3 Stg2 1 0
4 Stg2 0 0
5 Stg2 0 0
6 Stg2 0 0



But I want the toggle to go high for the preceding row as shown in col5.Any ideas are greatly appreciated.

Sorry, there is no NEXT operator (prophesy is limited to the select few).

But you can sort them the opposite way and HOLD, then use LAST in Define against the Hold file, and sort correctly for the report.

fex, here's a way to think about it:
make your flag a cumulative flag, and then you'll be wanting to select the max value per stg.
COMPUTE COL3/I5=IF COL2 NE LAST COL2 THEN 1 ELSE LAST COL3 + 1 ;
gives you
col1 col2 col3
1 Stg1 1
2 Stg1 2
3 Stg2 1
4 Stg2 2
5 Stg2 3
6 Stg2 4
now, you'll be wanting to flag the max of col3 for each Stg value.

I guess my table doesn't make sense. Here is a better version...
col1 col2 col3 col4 col5
1 Stg1 0 0 0
2 Stg1 0 0 1
3 0 Stg2 1 0
4 0 Stg2 0 0
5 0 Stg2 0 0
6 0 Stg2 0 0

Here is some code that may get you what you are looking for.

TABLE FILE CAR
SUM CAR AS 'NXTCAR'
COMPUTE LSTCAR/A16=LAST CAR;
BY COUNTRY NOPRINT
BY CAR NOPRINT
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS NXTCAR FORMAT FOCUS INDEX LSTCAR
END
-RUN
JOIN CAR IN CAR TO LSTCAR IN NXTCAR
-RUN
TABLE FILE CAR
SUM SALES NXTCAR
BY COUNTRY
BY CAR
END

Thank you all..

I will try these and let you guys know.

Thanks again

Here's another example that may help.
TOG1 flags the first car in the country
TOG2 flags the last car in the country

DEFINE FILE CAR
CNT/I5 = 1;
END
-*
TABLE FILE CAR
SUM CNT
BY COUNTRY
PRINT
COMPUTE CNTR/I5 = IF COUNTRY EQ LAST COUNTRY THEN CNTR + 1 ELSE 1;
BY COUNTRY
BY CAR
ON TABLE HOLD
END
-*
DEFINE FILE HOLD
TOG1/I1 = IF CNTR EQ 1 THEN 1 ELSE 0;
TOG2/I1 = IF CNTR EQ CNT THEN 1 ELSE 0;
END
TABLE FILE HOLD
PRINT
COUNTRY
CNT
CAR
TOG1
TOG2
END

Jim

Same as above without the hold file.

DEFINE FILE CAR
CNT/I5 = 1;
END
-*
TABLE FILE CAR
SUM CNT
BY COUNTRY
PRINT
COMPUTE CNTR/I5 = IF COUNTRY EQ LAST COUNTRY THEN CNTR + 1 ELSE 1;
COMPUTE TOG1/I1 = IF CNTR EQ 1 THEN 1 ELSE 0;
COMPUTE TOG2/I1 = IF CNTR EQ CNT THEN 1 ELSE 0;
BY COUNTRY
BY CAR
END

Jim

This is piggy solution and hopefully maybe others can supply a more elegant solution.

This is assuming that the file is already in the proper sort order for the last command.

In a define build a row counter:
cntr/i9=last cntr + 1;

Move your compute for col4 to a define. Then table your file with cols 1-4 and sort by highest cntr and then hold it. The file should now be in inverse order.

Now do a define on the hold file for col5 using the last commmand as done for col4. Then table the file and sort by cntr which would bring the file back to its original order.

..... or simply use FML, where you can reference virtually any cell you want, preceding or following the row in which your current cell exists.

Less data handling, "easier" to maintain ......

Just a thought .......

T

Hello All

Sorry for not responding sooner!

Thank you all for your responses. Since this was only a part of a much complex code and the data I am pulling into the report is huge, we have decided to put this requirement on hold. But I would still like to explore the possibilities of doing it in a simpler way and surely keep you all posted.

I did work on implementing counters but that didnt help me.

Tony A - I am not well versed with FML. Could you provide some mock code that I can look at.


Thanks again

Fex08,

As per your example, supposing that your file had three columns within it, called Col1, Col2 & Col3. FML that would create the required output for you would be -

   TABLE FILE fmlsheet
   PRINT Col2
         Col3
   COMPUTE Col4/D1 = 0;
   COMPUTE Col5/D1 = 0;
   FOR Col1
   1 LABEL C1 OVER
   2 LABEL C2 OVER
   3 LABEL C3 OVER
   4 LABEL C4 OVER
   5 LABEL C5 OVER
   6 LABEL C6 OVER
   RECAP C2(3) = IF C2(2) EQ 'Stg2' AND C1(1) EQ 'Stg1' THEN 1 ELSE 0; OVER
   RECAP C3(3) = IF C3(2) EQ 'Stg2' AND C2(1) EQ 'Stg1' THEN 1 ELSE 0; OVER
   RECAP C4(3) = IF C4(2) EQ 'Stg2' AND C3(1) EQ 'Stg1' THEN 1 ELSE 0; OVER
   RECAP C5(3) = IF C5(2) EQ 'Stg2' AND C4(1) EQ 'Stg1' THEN 1 ELSE 0; OVER
   RECAP C6(3) = IF C6(2) EQ 'Stg2' AND C5(1) EQ 'Stg1' THEN 1 ELSE 0; OVER
   RECAP C1(4) = IF C2(2) EQ 'Stg2' AND C1(1) EQ 'Stg1' THEN 1 ELSE 0; OVER
   RECAP C2(4) = IF C3(2) EQ 'Stg2' AND C2(1) EQ 'Stg1' THEN 1 ELSE 0; OVER
   RECAP C3(4) = IF C4(2) EQ 'Stg2' AND C3(1) EQ 'Stg1' THEN 1 ELSE 0; OVER
   RECAP C4(4) = IF C5(2) EQ 'Stg2' AND C4(1) EQ 'Stg1' THEN 1 ELSE 0; OVER
   RECAP C5(4) = IF C6(2) EQ 'Stg2' AND C5(1) EQ 'Stg1' THEN 1 ELSE 0;
   END

I will send a PM containing a more felexible approach when I have time.

T