SET BYDISPLAY = ON
-SET &PASS    = 0 ;
-*
-DREDO
-*
-SET &PASS = &PASS + 1;
-IF  &PASS = 3 GOTO AJOINS;
-SET &SV = 'PASS'||&PASS ;
-*
DEFINE FILE CLMDER
DERCLAIM/A10 = DERCLNO||DERWKNO;
CNTR/I9 = IF DERCLAIM EQ LAST DERCLAIM THEN LAST CNTR + 1 ELSE 1;
DRMK&PASS.EVAL/A2 = DERRMKCD;
END
-*
TABLE FILE CLMDER
PRINT DERRMKCD
      DERCLNO
      DERWKNO
DRMK&PASS.EVAL
BY DERCLAIM
WHERE CNTR EQ &PASS.EVAL
ON TABLE HOLD AS &SV
END
-*
-GOTO DREDO
-*
-*
-AJOINS
-*
JOIN CLEAR *
-*
SET ALL = ON
-*
JOIN
DERCLAIM IN PASS1 TO ALL DERCLAIM IN PASS2 AS J1
END
-*
TABLE FILE PASS1
PRINT
DRMK1
DRMK2
BY PASS1.DERCLAIM
ON TABLE PCHOLD FORMAT EXL2K
END
-*
-EXIT

TABLE FILE CAR
 LIST MODEL
 BY CAR
 BY COUNTRY
 ON TABLE HOLD AS HOLD1
END
-
DEFINE FILE HOLD1
 MODEL1/A24 = IF (HOLD1.LIST EQ 1) THEN MODEL ELSE '';
 MODEL2/A24 = IF (HOLD1.LIST EQ 2) THEN MODEL ELSE '';
 MODEL3/A24 = IF (HOLD1.LIST EQ 3) THEN MODEL ELSE '';
END
-*
TABLE FILE HOLD1
 SUM
  MAX.MODEL1 AS 'MODEL #1'
  MAX.MODEL2 AS 'MODEL #2'
  MAX.MODEL3 AS 'MODEL #3'
 BY COUNTRY
 BY CAR
 WHERE HOLD1.LIST LE 3 ;
END

DEFINE FILE CAR
 KEY/A26 = COUNTRY | CAR ;
 MODEL1/A24 = IF (KEY NE LAST KEY)   THEN MODEL ELSE LAST MODEL1 ;
 MODEL2/A24 = IF (KEY NE LAST KEY)   THEN '' ELSE
              IF (LAST MODEL2 EQ '') THEN MODEL ELSE LAST MODEL2 ;
 MODEL3/A24 = IF (KEY NE LAST KEY)   THEN '' ELSE
              IF (LAST MODEL2 EQ '') THEN '' ELSE
              IF (LAST MODEL3 EQ '') THEN MODEL ELSE LAST MODEL3 ;
END
-*
TABLE FILE CAR
 SUM
  LST.MODEL1
  LST.MODEL2
  LST.MODEL3
 BY COUNTRY
 BY CAR
END

quote:

Originally posted by Dan Satchell:
Here's an approach that might work under the right circumstances (i.e., all data in one table). I used the CAR file for this example and limited the output to 3 models per car. The LIST verb numbers each of the MODELs for easier identification in the subsequent DEFINEs. Note that if you have more than one key in your table, you must sort first by the lowest key that is appropriate for the field/column you want to target (CONTACT_PERSON in your case). This is the reason I sort by CAR first and then by COUNTRY in my first TABLE FILE, because MODEL is related to CAR, not COUNTRY. You must use the MAX. operator with the DEFINE fields in conjunction with the SUM verb in the second query, but FST. or LST. can be used for other columns needed in the query because all other values should be identical for records with the same keys. Use of the file qualifier with the LIST name (HOLD1.LIST) is required to keep the parser from getting confused by use of the keyword LIST. If you must JOIN to other tables this approach probably won't work unless you operate on the one table first before joining to the others (which won't be efficient if the other tables are in SAP - unless they are very small tables).

TABLE FILE CAR
 LIST MODEL
 BY CAR
 BY COUNTRY
 ON TABLE HOLD AS HOLD1
END
-
DEFINE FILE HOLD1
 MODEL1/A24 = IF (HOLD1.LIST EQ 1) THEN MODEL ELSE '';
 MODEL2/A24 = IF (HOLD1.LIST EQ 2) THEN MODEL ELSE '';
 MODEL3/A24 = IF (HOLD1.LIST EQ 3) THEN MODEL ELSE '';
END
-*
TABLE FILE HOLD1
 SUM
  MAX.MODEL1 AS 'MODEL #1'
  MAX.MODEL2 AS 'MODEL #2'
  MAX.MODEL3 AS 'MODEL #3'
 BY COUNTRY
 BY CAR
 WHERE HOLD1.LIST LE 3 ;
END

NAME1/A80=IF NAME1 EQ '' THEN CONTACT_NAME ELSE NAME1;
NAME2/A80=IF NAME1 NE '' AND NAME1 NE CONTACT_NAME THEN CONTACT_NAME ELSE '';
NAME3/A160=NAME1 | NAME2;

quote:

Originally posted by Dan Satchell:
Here's an easier approach than the one I posted earlier. I concatenate the key fields together to make the DEFINEs much easier to code. Again, you can use the FST. or LST. operators with other columns that must also be included in the output. This is particularly true for numeric fields to avoid summing duplicate values. This approach will probably work in conjunction with JOINs to other tables.

DEFINE FILE CAR
 KEY/A26 = COUNTRY | CAR ;
 MODEL1/A24 = IF (KEY NE LAST KEY)   THEN MODEL ELSE LAST MODEL1 ;
 MODEL2/A24 = IF (KEY NE LAST KEY)   THEN '' ELSE
              IF (LAST MODEL2 EQ '') THEN MODEL ELSE LAST MODEL2 ;
 MODEL3/A24 = IF (KEY NE LAST KEY)   THEN '' ELSE
              IF (LAST MODEL2 EQ '') THEN '' ELSE
              IF (LAST MODEL3 EQ '') THEN MODEL ELSE LAST MODEL3 ;
END
-*
TABLE FILE CAR
 SUM
  LST.MODEL1
  LST.MODEL2
  LST.MODEL3
 BY COUNTRY
 BY CAR
END