I have a hold file that will have 2 columns. It will be small, less than 10 rows.

The data looks like this:

  
COSTCENTER      DESCRIPTION
12345           COST CENTER A
23456           COST CENTER B
34567           COST CENTER C

So looking at that hold file, in this instance there's 3 cost centers each with a description... but the number of cost centers may vary when the report is ran each time.

I would like to use those cost centers in a DEFINE statement.... each cost center getting its own entry. The example below is STATIC but would like to make it dynamic.

  
DEFINE FILE PREP
METRIC1/D15.2 = IF COSTCENTER = &COSTCENTER1 THEN AMOUNT ELSE 0;
METRIC2/D15.2 = IF COSTCENTER = &COSTCENTER2 THEN AMOUNT ELSE 0;
METRIC3/D15.2 = IF COSTCENTER = &COSTCENTER3 THEN AMOUNT ELSE 0;
-*this needs to loop for as many cost centers as there are in the hold file
END

and then later, in a PRINT step, each of those cost centers would get its own entry (the metrics from the define step) with the description of the cost center pulled in.

TABLE FILE PREP
SUM
METRIC1 AS '&COSTCENTER1DESC' -* THIS WOULD BE THE DESCRIPTION OF COST CENTER 1
METRIC2 AS '&COSTCENTER2DESC' -* THIS WOULD BE THE DESCRIPTION OF COST CENTER 2
METRIC3 AS '&COSTCENTER3DESC' -* THIS WOULD BE THE DESCRIPTION OF COST CENTER 3
-*this needs to loop for as many cost centers as there are in the hold file
END

What would be the best way to approach this?

I've done some reading on -READ loops but it isn't entirely clear to me how to do that or what the best approach would be. Any help or direction would be very much appreciated.
Thanks,
Jason

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

Hello Jason,
I can help you with looping part, probably you can figure it out from there.

1. Use -READFILE and REPEAT to iterate for no. of records in the HOLD file

Example:

-REPEAT LOOPTEMP FOR &A FROM 1 TO &LINES
-READFILE "YOUR TEMP FILE"
-* you can access hold file data using &FILED_NAME1, &FIEL_NAME2 etc. (in your case &COSTCENTER etc.)
...
...
Write your code here of whatever looping is required. For each iteration, each row's data in your hold file can be accessed.
...

-LOOPTEMP

Hope this helps and works out for you.

Another approach is to TABLE your costcenters as an ALPHA file containing the DEFINE lines. Next you can simply -INCLUDE that ALPHA file in your DEFINE.

Or better yet, -INCLUDE it directly as COMPUTES in your final TABLE request. In that case you can generate the appropriate descriptions at the same time.

TABLE FILE COSTCENTER
SUM
    COMPUTE LINENR/I1 = LAST LINENR +1; NOPRINT
    COMPUTE LINE/A512 = 'COMPUTE METRIC' || FPRINT(LINENR, 'I1', 'A1') || '/D15.2 = IF COSTCENTER EQ ''' ||
        COSTCENTER || ''' THEN AMOUNT ELSE 0; AS ''' || DESCRIPTION || ''';
BY COSTCENTER NOPRINT
ON TABLE HOLD AS LINES FORMAT ALPHA
END
-RUN

TABLE FILE PREP
SUM
-INCLUDE LINES
END

Have you thought about doing something like this?

TABLE FILE PREP
SUM AMOUNT
ACROSS COSTCENTER NOPRINT
ACROSS DESCRIPTION
....

As in this example

TABLE FILE GGSALES
SUM DOLLARS
ACROSS ST NOPRINT
ACROSS STCD AS ''
BY REGION
END

T

quote:

Originally posted by Wep5622:
Another approach is to TABLE your costcenters as an ALPHA file containing the DEFINE lines. Next you can simply -INCLUDE that ALPHA file in your DEFINE.

Or better yet, -INCLUDE it directly as COMPUTES in your final TABLE request. In that case you can generate the appropriate descriptions at the same time.

TABLE FILE COSTCENTER
SUM
    COMPUTE LINENR/I1 = LAST LINENR +1; NOPRINT
    COMPUTE LINE/A512 = 'COMPUTE METRIC' || FPRINT(LINENR, 'I1', 'A1') || '/D15.2 = IF COSTCENTER EQ ''' ||
        COSTCENTER || ''' THEN AMOUNT ELSE 0; AS ''' || DESCRIPTION || ''';
BY COSTCENTER NOPRINT
ON TABLE HOLD AS LINES FORMAT ALPHA
END
-RUN

TABLE FILE PREP
SUM
-INCLUDE LINES
END

This approach intrigued me...I tried, but the include step is what threw it off. I got an error saying cannot include the specified resource. Can you include a hold file that is generated within the same fex?

It may have unbalanced apostrophes in Wep Code

The following do work:

TABLE FILE CAR
SUM
    COMPUTE LINENR/I1 = LAST LINENR +1; NOPRINT
    COMPUTE LINE/A512 = 'COMPUTE METRIC' || FPRINT(LINENR, 'I1', 'A1') || '/D15.2 = IF COUNTRY EQ ''FRANCE'' THEN RETAIL_COST ELSE 0; AS ''DESCRIPTION''';
BY CAR NOPRINT
ON TABLE HOLD AS LINES FORMAT ALPHA
END
-RUN

TABLE FILE CAR
SUM
-INCLUDE LINES
END

To find out where is the error run only the first TABLE FILE without holding the data and have trace on to see generated code.

2 suggestions:
1. add ON TABLE SET HOLDLIST PRINTONLY
2. use FILEDEF LINES DISK LINES.FEX before the TABLE command.