Hi All,

I am looking for some logic to display column7 row1 value(marked in red) is displayed in third row of a column 6 row3(marked in red) and column7 row 2 value(marked in green) is displayed in fourth row of a column6 row4(marked in green)

Thank you in advance.

Column1 Column2 Column3 Column4 Column5 Column6 Column7
1 T ABC -100 2500 2400
1 F XYZ 100 1000 900
2 T ABC 10 2400 2390
2 F XYZ 10 900 890

This message has been edited. Last edited by: <Kathryn Henning>,

-* FocalPoint Test

-GOTO :CODE
-* DATA:
Column1	Column2	Column3	Column4	Column5	Column6	Column7
1	T	ABC		-100	2500	2400
1	F		XYZ	100	1000	900
2	T	ABC		10	4400	2390
2	F		XYZ	10	4900	890

-* MASTER FILE
FILENAME=FPDATA,SUFFIX=TABT, $
FIELDNAME=COL1, USAGE=I8, ACTUAL=A8, $
FIELDNAME=COL2, USAGE=A3, ACTUAL=A3, $
FIELDNAME=COL3, USAGE=A3, ACTUAL=A3, $
FIELDNAME=COL4, USAGE=A3, ACTUAL=A3, $
FIELDNAME=COL5, USAGE=I8, ACTUAL=A8, $
FIELDNAME=COL6, USAGE=I8, ACTUAL=A8, $
FIELDNAME=COL7, USAGE=I8, ACTUAL=A8, $

-:CODE
FILEDEF FPDATA DISK SESSION/FPDATA.TXT 
TABLE FILE FPDATA
PRINT COL1
      COL2
	  COL3
	  COL4
	  COL5 
      COMPUTE FIRST/I8  = IF COL1 NE LAST COL1 THEN COL7 ELSE FIRST; NOPRINT
      COMPUTE SECOND/I8 = IF COL1 EQ LAST COL1 THEN COL7 ELSE SECOND; NOPRINT
      COMPUTE NCOL6/I8  = IF COL1 GT 1 AND COL1 NE LAST COL1 THEN LAST FIRST ELSE 
                          IF COL1 GT 1 AND COL1 EQ LAST COL1 THEN LAST SECOND ELSE COL6; AS 'COL6'
      COL7
END

This will produce exactly what you want, provided it is indeed as simple in reality as your example here.

Hi GamP,

Thank you so much for your response...

Actually I am looking for dynamic way to display last two column data. Could you please let me know if any option is there.


Column1 Column2 Column3 Column4 Column5 Column6 Column7
1 T ABC -100 2500 2400
1 F XYZ 100 1000 1100
2 T ABC -10 2400 2390
2 F XYZ 10 1100 1110
3 T ABC -50 2390 2340
3 F XYZ 50 1110 1240

Have you tried running my code with the data you provide here?
Don't think so, otherwise you would have seen that it also works for this new data set. It will work for any data set, provided it follows the same rule and layout.

Hi GamP,

I tried implementing your code but of no use... 3rd and 4th row is working but from 5th and 6th row its showing 3rd and 4th row values.

This is the code I used:-
COMPUTE FIRST/I8 = IF XTRANS_NO NE LAST XTRANS_NO THEN NEW_TOTAL_PEND ELSE FIRST;
COMPUTE SECOND/I8 = IF XTRANS_NO EQ LAST XTRANS_NO THEN NEW_TOTAL_PEND ELSE SECOND;
COMPUTE NCOL6/I8 = IF XTRANS_NO GT '0001' AND XTRANS_NO NE LAST XTRANS_NO THEN LAST FIRST ELSE
IF XTRANS_NO NE '0001' AND XTRANS_NO EQ LAST XTRANS_NO THEN LAST SECOND ELSE PENDING;

Output based on above code
XTRANS_NO TRAN_FLG REGION FROM_OFFICE TO_OFFICE PEND_OUT FIRST SECOND NCOL6
00001 F 03 03-BALTIMORE_T21 Missing -10 12100 0 12110
00001 T 03 Missing 12-NHC BALTIMORE_T0T 10 12100 3397 3387
00002 F 03 03-BALTIMORE_T21 Missing -10 12100 3397 12100
00002 T 03 Missing 12-NHC BALTIMORE_T0T 10 12100 3397 3397
00003 F 03 03-BALTIMORE_T21 Missing -10 12100 3397 12100
00003 T 03 Missing 12-NHC BALTIMORE_T0T 10 12100 3397 3397



Required Output
XTRANS_NO TRAN_FLG REGION FROM_OFFICE TO_OFFICE PEND_OUT PENDING NEW_TOTAL_PEND
0001 F 3 03-BALTIMORE_T21 Missing -10 12,110 12,100
0001 T 3 Missing 12-NHC BALTIMORE_T0T 10 3,387 3,397
0002 F 3 03-BALTIMORE_T21 Missing -20 12,100 12,080
0002 T 3 Missing 12-NHC BALTIMORE_T0T 20 3,397 3,417
0003 F 3 03-BALTIMORE_T21 Missing -40 12,080 12,040
0003 T 3 Missing 12-NHC BALTIMORE_T0T 40 3,417 3,457
0004 F 3 03-BALTIMORE_T21 Missing -10 12,040 12,030
0004 T 3 Missing 12-NHC BALTIMORE_T0T 10 3,457 3,467

Regards,
Sunil

This message has been edited. Last edited by: M A Sunil,

Run this procedure as is. It will produce the desired output. Again, hopefully you real life situation is as simple as your example.

-* FocalPoint Test

-* DATA:
EX -LINES 9 EDAPUT FOCTEMP,FPDATA,CV,FILE
0001 F 3 03-BALTIMORE_T21      Missing               -10      12.110      12.100
0001 T 3 Missing               12-NHC BALTIMORE_T0T   10       3.387       3.397
0002 F 3 03-BALTIMORE_T21      Missing               -20        .000      12.080
0002 T 3 Missing               12-NHC BALTIMORE_T0T   20        .000       3.417
0003 F 3 03-BALTIMORE_T21      Missing               -40        .000      12.040
0003 T 3 Missing               12-NHC BALTIMORE_T0T   40        .000       3.457
0004 F 3 03-BALTIMORE_T21      Missing               -10        .000      12.030
0004 T 3 Missing               12-NHC BALTIMORE_T0T   10        .000       3.467

-* MASTER FILE
EX -LINES 10 EDAPUT MASTER,FPDATA,CV,FILE
FILENAME=FPDATA,SUFFIX=FIX, $
FIELDNAME=XTRANS_NO     , USAGE=I4   , ACTUAL=A5,  $
FIELDNAME=TRAN_FLG      , USAGE=A2   , ACTUAL=A2,  $
FIELDNAME=REGION        , USAGE=A2   , ACTUAL=A2,  $
FIELDNAME=FROM_OFFICE   , USAGE=A22  , ACTUAL=A22, $
FIELDNAME=TO_OFFICE     , USAGE=A22  , ACTUAL=A22, $
FIELDNAME=PEND_OUT      , USAGE=I4   , ACTUAL=A4,  $
FIELDNAME=PENDING       , USAGE=D8.3 , ACTUAL=A12, $
FIELDNAME=TOTAL_PENDING , USAGE=D8.3 , ACTUAL=A12, $

-:CODE
FILEDEF FPDATA DISK FPDATA.FTM
TABLE FILE FPDATA
PRINT XTRANS_NO
      TRAN_FLG
	  REGION
	  FROM_OFFICE
	  TO_OFFICE
	  PEND_OUT
	  PENDING
      COMPUTE FIRST/D8.3       = IF XTRANS_NO NE LAST XTRANS_NO THEN TOTAL_PENDING ELSE FIRST; NOPRINT
      COMPUTE SECOND/D8.3      = IF XTRANS_NO EQ LAST XTRANS_NO THEN TOTAL_PENDING ELSE SECOND; NOPRINT
      COMPUTE NEW_PENDING/D8.3 = IF XTRANS_NO GT 1 AND XTRANS_NO NE LAST XTRANS_NO THEN LAST FIRST ELSE
                                 IF XTRANS_NO GT 1 AND XTRANS_NO EQ LAST XTRANS_NO THEN LAST SECOND ELSE TOTAL_PENDING;
      TOTAL_PENDING
END

Sunil,
I am not quite sure if I completely understood the problem but I have this sample code against CAR file that might help.
TABLE FILE CAR
PRINT COUNTRY
CAR
COMPUTE LAST_RETAIL/D12=LAST RETAIL_COST; NOPRINT
COMPUTE LAST_LAST_RETAIL/D12=LAST LAST_RETAIL; NOPRINT
COMPUTE LAST_COUNTRY/A50=LAST COUNTRY; NOPRINT
COMPUTE LAST_LAST_COUNTRY/A50=LAST LAST_COUNTRY; NOPRINT
COMPUTE NEW_DEALER_COST/D12 = IF LAST_LAST_COUNTRY EQ ' ' THEN DEALER_COST ELSE LAST_LAST_RETAIL;
RETAIL_COST
END

Thank you very much GamP and Shahram for your quick response.

We tried this option but its not getting the desired output so we have changed the design to get our require result.

Regards,
Sunil