My code (complete code below) contains a series of If-Then statements that could result in 10 possible columns. My problem is that if there are no values for the column, then it does not print or display. How can I display the columns when there are no values? Also, is there a way to loop the following code so that I am not locked in to only the 10 possible combinations? (I would like it to be dynamic.)

If-Then Code:
DEFINE FILE CVTMAST
SAMEKEY/A1=IF CVTKEY EQ LAST CVTKEY THEN 'Y' ELSE 'N';
SAMETYP/A1=IF SAMEKEY EQ 'Y' AND (TRANS NE LAST TRANS OR TORQ NE LAST
TORQ OR CONVL NE LAST CONVL) THEN 'N' ELSE 'Y';
SAMEPRT/A1=IF KEY2 EQ LAST KEY2 THEN 'Y' ELSE 'N';
PART1/A6=
IF SAMEKEY EQ 'N' AND TRANS EQ 'Y' THEN 'TR1' ELSE
IF SAMEKEY EQ 'N' AND TORQ EQ 'Y' THEN 'TO1' ELSE
IF SAMEKEY EQ 'N' AND CONVL EQ 'Y' THEN 'CO1' ELSE
IF SAMETYP EQ 'N' AND TRANS EQ 'Y' THEN 'TR1' ELSE
IF SAMETYP EQ 'N' AND TORQ EQ 'Y' THEN 'TO1' ELSE
IF SAMETYP EQ 'N' AND CONVL EQ 'Y' THEN 'CO1' ELSE
IF SAMEPRT EQ 'Y' AND TRANS EQ 'Y' THEN 'TR1' ELSE
IF SAMEPRT EQ 'Y' AND TORQ EQ 'Y' THEN 'TO1' ELSE
IF SAMEPRT EQ 'Y' AND CONVL EQ 'Y' THEN 'CO1' ELSE
IF SAMETYP EQ 'Y' AND TRANS EQ 'Y' THEN 'TR2' ELSE
IF SAMETYP EQ 'Y' AND TORQ EQ 'Y' THEN 'TO2' ELSE
IF SAMETYP EQ 'Y' AND CONVL EQ 'Y' THEN 'CO2' ELSE '0';
PART/A8=PART1||TYPE;

END

Complete code:
DEFINE FILE SVCCLAIM
KEY/A36=SC_VEH_KEY||SC_DEALER_NO||SC_WO_NO||SC_WOL_NO;
END

TABLE FILE SVCCLAIM.SC_PRT_NBR
SUM
SC_ADJ_AMT

COMPUTE
TYPE/A3=IF SC_CLM_TYPE EQ 'FW' AND SC_PRT_NBR EQ 'SUBLTRENTX' THEN 'FWR'
ELSE IF SC_CLM_TYPE EQ 'FG' AND SC_PRT_NBR EQ 'SUBLTRENTX' THEN 'FGR'
ELSE IF SC_CLM_TYPE EQ 'CM' AND SC_PRT_NBR EQ 'SUBLTRENTX' THEN 'CMR'
ELSE IF SC_CLM_TYPE EQ 'SC' AND SC_PRT_NBR EQ 'SUBLTRENTX' THEN 'SCR'
ELSE IF SC_CLM_TYPE EQ 'FW' AND SC_PRT_NBR EQ 'SUBLTTOWXX' THEN 'FWT'
ELSE IF SC_CLM_TYPE EQ 'FG' AND SC_PRT_NBR EQ 'SUBLTTOWXX' THEN 'FGT'
ELSE IF SC_CLM_TYPE EQ 'CM' AND SC_PRT_NBR EQ 'SUBLTTOWXX' THEN 'CMT'
ELSE IF SC_CLM_TYPE EQ 'SC' AND SC_PRT_NBR EQ 'SUBLTTOWXX' THEN 'SCT'
ELSE 'ERR';
BY KEY
BY SC_CLM_TYPE
BY SC_PRT_NBR
BY SC_WO_NO
BY SC_WOL_NO
BY SC_RO_DATE
BY SC_CLM_DATE
BY SC_LST_DATE
WHERE SC_APPVD_CLM EQ 'Y'
WHERE SC_CSR_DATE EQ 1403
-*WHERE SC_LST_DATE EQ 03042014
WHERE SC_PRT_NBR EQ 'SUBLTRENTX' OR 'SUBLTTOWXX'
ON TABLE HOLD AS RENT FORMAT FOCUS INDEX KEY
END

TABLE FILE RENT
SUM
SC_ADJ_AMT
BY KEY
BY SC_WO_NO
ACROSS TYPE
ON TABLE HOLD AS RENT2 FORMAT FOCUS INDEX KEY
END

TABLE FILE RENT2
PRINT *
ON TABLE HOLD AS RENT3 FORMAT FOCUS INDEX KEY
END

DYNAM ALLOC FILE PPRICEM DA CEN.FOCUS.PROD.PPRICEM.DATA SHR
DEFINE FILE PPRICEM
PART_NO/A12 = EDIT(PR_PART_NO,'999999999999$$$');
END
TABLE FILE PPRICEM
SUM
PR_DESC
BY PART_NO
WHERE PART_NO LIKE '3%' OR 'C%'
ON TABLE HOLD AS PARTHOLD FORMAT FOCUS INDEX PART_NO
END

DYNAM FREE FILE PPRICEM

JOIN SC_PRT_NBR IN SVCCLAIM TO PART_NO IN PARTHOLD AS J2

-INCLUDE INCLDMDL
DEFINE FILE SVCCLAIM ADD

-INCLUDE DEFDATE

PRE3/A5 = EDIT(SC_PRT_NBR,'999$$$$$$$$$');
LAST2/A2 = EDIT(SC_PRT_NBR,'$$$$$$$$$$99');
PRE5/A5 = EDIT(SC_PRT_NBR,'99999$$$$$$$');
TRANS/A1 = IF PRE3 EQ '310' OR 'C10' AND
SC_DLR_AMT GT 600 AND
PR_DESC NOT LIKE '%SHIFT CONTROL%' AND
PR_DESC NOT LIKE '%CONTROL UNIT%' THEN 'Y' ELSE 'N';
REMAN/A1 = IF LAST2 EQ 'RE' THEN 'Y' ELSE 'N';
CVT/A1 = IF TRANS_TYPE_CD EQ 'CVT' THEN 'Y' ELSE 'N';
TORQC/A1 = IF PRE5 EQ '31100' AND
PR_DESC NOT LIKE '%CLUTCH%' THEN 'Y' ELSE 'N';
CONVL/A1 = IF PRE5 EQ '31705' THEN 'Y' ELSE 'N';
CVTKEY/A36=SC_VEH_KEY||SC_DEALER_NO||SC_WO_NO||SC_WOL_NO;
KEY2/A50=SC_VEH_KEY||SC_DEALER_NO||SC_WO_NO||SC_WOL_NO||SC_PRT_NBR;
-*PD/A70=SC_PRT_NBR||PR_DESC;
PD/A70=SC_PRT_NBR;
END
TABLE FILE SVCCLAIM.SC_PRT_NBR

PRINT
SC_WO_NO
SC_WOL_NO
SC_RO_DATE
SC_CLM_DATE
SC_LST_DATE
SC_CLM_AMT
SC_CLM_TYPE AS TYPE
SC_PRT_NBR
SC_ADJ_AMT AS AMT
SC_DLR_AMT
PR_DESC
PD
TRANS
CVT
TORQC
CONVL
CVTKEY
BY KEY2
WHERE SC_APPVD_CLM EQ 'Y'
WHERE SC_CSR_DATE EQ 1403
-*WHERE SC_LST_DATE EQ 03042014
WHERE TRANS EQ 'Y' OR TORQC EQ 'Y' OR CONVL EQ 'Y'
WHERE SC_PRT_QTY GT 0
ON TABLE HOLD AS CVTMAST FORMAT FOCUS INDEX CVTKEY
END

DEFINE FILE CVTMAST
SAMEKEY/A1=IF CVTKEY EQ LAST CVTKEY THEN 'Y' ELSE 'N';
SAMETYP/A1=IF SAMEKEY EQ 'Y' AND (TRANS NE LAST TRANS OR TORQ NE LAST
TORQ OR CONVL NE LAST CONVL) THEN 'N' ELSE 'Y';
SAMEPRT/A1=IF KEY2 EQ LAST KEY2 THEN 'Y' ELSE 'N';
PART1/A6=
IF SAMEKEY EQ 'N' AND TRANS EQ 'Y' THEN 'TR1' ELSE
IF SAMEKEY EQ 'N' AND TORQ EQ 'Y' THEN 'TO1' ELSE
IF SAMEKEY EQ 'N' AND CONVL EQ 'Y' THEN 'CO1' ELSE
IF SAMETYP EQ 'N' AND TRANS EQ 'Y' THEN 'TR1' ELSE
IF SAMETYP EQ 'N' AND TORQ EQ 'Y' THEN 'TO1' ELSE
IF SAMETYP EQ 'N' AND CONVL EQ 'Y' THEN 'CO1' ELSE
IF SAMEPRT EQ 'Y' AND TRANS EQ 'Y' THEN 'TR1' ELSE
IF SAMEPRT EQ 'Y' AND TORQ EQ 'Y' THEN 'TO1' ELSE
IF SAMEPRT EQ 'Y' AND CONVL EQ 'Y' THEN 'CO1' ELSE
IF SAMETYP EQ 'Y' AND TRANS EQ 'Y' THEN 'TR2' ELSE
IF SAMETYP EQ 'Y' AND TORQ EQ 'Y' THEN 'TO2' ELSE
IF SAMETYP EQ 'Y' AND CONVL EQ 'Y' THEN 'CO2' ELSE '0';
PART/A8=PART1||TYPE;

END

TABLE FILE CVTMAST
SUM
PD
AMT
BY CVTKEY
ACROSS PART
ON TABLE HOLD AS CVTPAR2 FORMAT FOCUS INDEX CVTKEY
END


JOIN CVTKEY IN CVTPAR2 TO KEY IN RENT3 AS J3

TABLE FILE CVTPAR2
PRINT
CVTKEY
PDCO1FG
AMTCO1FG
PDCO1FW
AMTCO1FW
PDCO1SC
AMTCO1SC
PDTO1FG
AMTTO1FG
PDTO1FW
AMTTO1FW
PDTO1SC
AMTTO1SC
PDTR1CM
AMTTR1CM
PDTR1FG
AMTTR1FG
PDTR1FW
AMTTR1FW
PDTR1SC
AMTTR1SC
SC_CMR AS CMRNT
SC_FGR AS FGRNT
SC_FWR AS FWRNT
SC_SCR AS SCRNT
SC_CMT AS CMTOW
SC_FGT AS FGTOW
SC_FWT AS FWTOW
SC_SCT AS SCTOW
-*ON TABLE HOLD AS CVTPAR3 FORMAT FOCUS INDEX KEY
ON TABLE SAVE AS TRANSFER FORMAT EXL2K
END

DYNAM ALLOC FILE PNCMSTR DA NMCV.VSW1600.PNC.HEADER.MASTER SHR
TABLE FILE PNCMSTR

PRINT
PN_DESC PN_COMPONENT BY PN_CODE
ON TABLE HOLD AS PNCS FORMAT FOCUS INDEX PN_CODE
END
DYNAM FREE FILE PNCMSTR

JOIN SC_PNC IN SVCCLAIM TO PN_CODE IN PNCS AS J4

DEFINE FILE SVCCLAIM
KEY/A36=SC_VEH_KEY||SC_DEALER_NO||SC_WO_NO||SC_WOL_NO;
END

TABLE FILE SVCCLAIM
SUM
SC_CLM_AMT
BY KEY
BY SC_DEALER_NO
ACROSS SC_CLM_TYPE
WHERE SC_APPVD_CLM EQ 'Y'
WHERE SC_CSR_DATE EQ 1403
-*WHERE SC_LST_DATE EQ 03042014
WHERE PN_COMPONENT EQ 'AT'
ON TABLE HOLD AS ATHOLD FORMAT FOCUS INDEX KEY
END

TABLE FILE ATHOLD
PRINT *
ON TABLE HOLD AS ATHOLD2 FORMAT FOCUS INDEX KEY
END

JOIN KEY IN ATHOLD2 TO CVTKEY IN CVTPAR2 AS J5
JOIN KEY IN ATHOLD2 TO KEY IN RENT3 AS J6

DEFINE FILE ATHOLD2
SC/D12.2=SC_SC-(SC_SCR+SC_SCT);
FW/D12.2=SC_FW-(SC_FWR+SC_FWT);
FG/D12.2=SC_FG-(SC_FGR+SC_FGT);
MATCH/A1=IF CVTKEY EQ KEY THEN 'Y' ELSE 'N';
END

TABLE FILE ATHOLD2
PRINT
KEY
SC
FW
FG
SC_CMR AS CMRNT
SC_FGR AS FGRNT
SC_FWR AS FWRNT
SC_SCR AS SCRNT
SC_CMT AS CMTOW
SC_FGT AS FGTOW
SC_FWT AS FWTOW
SC_SCT AS SCTOW
WHERE MATCH EQ 'N'
ON TABLE HOLD AS ATHOLD3 FORMAT FOCUS INDEX KEY
END

TABLE FILE ATHOLD3
PRINT *
ON TABLE SAVE AS TRNSFER2 FORMAT EXL2K
END

This message has been edited. Last edited by: FP Mod Chuck,

casaler,

when posting sample or code sample, please use the code tag (last one on ribbon):

</>

quote:

My problem is that if there are no values for the column, then it does not print or display. How can I display the columns when there are no values?

As per the above, I think that your issue is due to the use of ACROSS. If there is no value to display it won't be displayed using ACROSS.
You need to create (DEFINE/COMPUTE) each columns and then display them.

Sample

DEFINE FILE CAR
DUMMY1 /D9 MISSING ON = IF COUNTRY EQ 'ENGLAND' THEN RETAIL_COST ELSE MISSING;
DUMMY2 /D9 MISSING ON = IF COUNTRY EQ 'FRANCE'  THEN RETAIL_COST ELSE MISSING;
DUMMY3 /D9 MISSING ON = IF COUNTRY EQ 'ITALY'   THEN RETAIL_COST ELSE MISSING;
DUMMY4 /D9 MISSING ON = IF COUNTRY NE 'ENGLAND' THEN RETAIL_COST ELSE MISSING;
DUMMY5 /D9 MISSING ON = IF COUNTRY EQ 'USA'     THEN RETAIL_COST ELSE MISSING;
END
TABLE FILE CAR
SUM DUMMY1
    DUMMY2
    DUMMY3
    DUMMY4
    DUMMY5
BY COUNTRY
BY CAR
ON TABLE SET PAGE-NUM NOLEAD
END
-RUN

Using ACROSS fifth value for DUMMY is not printed

DEFINE FILE CAR
DUMMY /D2 MISSING ON = IF COUNTRY EQ 'ENGLAND' THEN 1
                  ELSE IF COUNTRY EQ 'FRANCE'  THEN 2
                  ELSE IF COUNTRY EQ 'ITALY'   THEN 3
                  ELSE IF COUNTRY NE 'ENGLAND' THEN 4
                  ELSE IF COUNTRY EQ 'USA'     THEN 5 ELSE MISSING;
END
TABLE FILE CAR
SUM RETAIL_COST
BY COUNTRY
BY CAR
ACROSS DUMMY
ON TABLE SET PAGE-NUM NOLEAD
END
-RUN

quote:

is there a way to loop the following code so that I am not locked in to only the 10 possible combinations?

As for the above, yes it's possible, but based on your code, I don't see any repetitive code that can be included in a loop since each IF has a different test to perform.
And furthermore, I don't see any 10 cases. TYPE has 9 conditions resulting in 9 different values. PART1 has 13 conditions resulting in 7 different values. PART is a combination of PART1 and TYPE, so it could potentially have 63 different values (9 * 7).

I may miss something...

This message has been edited. Last edited by: MartinY,

Thank you for the quick reply. I'll try your suggestion, but it may take me a while to do it. Once I've run it, I'll report back on the findings.

If you know what the columns should be then perhaps you can add the COLUMNS tag to the ACROSS to force the existence of all the values.

e.g.
TABLE FILE CVTMAST
SUM
PD
AMT
BY CVTKEY
ACROSS PART COLUMN {val1} AND {val2} AND {valn}
ON TABLE HOLD AS CVTPAR2 FORMAT FOCUS INDEX CVTKEY
END

Have you tried
SET EMPTYREPORT = ANSI

I would use the macgyver technique. Create a hold file with each value off something like car or some other constant table and then join back to your table with a left outer join, this way a blank record will be created if a value doesn't exist.