[SOLVED]Transpose with Comma

September 29, 2017, 02:29 PM

Tom Flynn

  
TABLE FILE CAR
PRINT
     COUNTRY
  BY COUNTRY NOPRINT
 ON TABLE HOLD AS HOLD1 FORMAT ALPHA
END
-RUN
-SET &XLINES = &LINES;
-SET &CNTR = 0;
-SET &COUNTRY_OUT = '';
-SET &COUNTRY     = '';
-REPEAT GET_DATA &XLINES TIMES
-READ HOLD1 NOCLOSE &COUNTRY.A10.
-SET &CNTR = &CNTR + 1;
-SET &COUNTRY_OUT = IF &CNTR EQ 1 THEN &COUNTRY || ',' ELSE 
-                   IF &CNTR EQ &XLINES THEN &COUNTRY_OUT || &COUNTRY ELSE &COUNTRY_OUT || &COUNTRY || ',';
-GET_DATA
-TYPE &COUNTRY_OUT
-EXIT

September 29, 2017, 03:07 PM

jfr99

Or this ...

TABLE FILE CAR
SUM
COMPUTE CNTR/I5 = CNTR + 1;
COUNTRY
BY COUNTRY NOPRINT
ON TABLE HOLD AS HLD01 FORMAT ALPHA
END
-RUN
TABLE FILE HLD01
PRINT
COMPUTE ALL_CNTRY/A300V = IF CNTR EQ 1 THEN COUNTRY ELSE ALL_CNTRY || ', ' | COUNTRY;
BY TOTAL HIGHEST 1 CNTR NOPRINT
END
-RUN

September 29, 2017, 04:11 PM

Don Garland

Or dis....

DEFINE FILE CAR
POINTER/A10 = LAST COUNTRY;
END
TABLE FILE CAR
PRINT
COMPUTE CLIST/A100 = IF POINTER NE '' THEN COUNTRY || ',' ELSE COUNTRY;
ACROSS HIGHEST COUNTRY NOPRINT
END
-RUN

October 02, 2017, 01:05 PM

Sandeep

quote:

Originally posted by Tom Flynn:

  
TABLE FILE CAR
PRINT
     COUNTRY
  BY COUNTRY NOPRINT
 ON TABLE HOLD AS HOLD1 FORMAT ALPHA
END
-RUN
-SET &XLINES = &LINES;
-SET &CNTR = 0;
-SET &COUNTRY_OUT = '';
-SET &COUNTRY     = '';
-REPEAT GET_DATA &XLINES TIMES
-READ HOLD1 NOCLOSE &COUNTRY.A10.
-SET &CNTR = &CNTR + 1;
-SET &COUNTRY_OUT = IF &CNTR EQ 1 THEN &COUNTRY || ',' ELSE 
-                   IF &CNTR EQ &XLINES THEN &COUNTRY_OUT || &COUNTRY ELSE &COUNTRY_OUT || &COUNTRY || ',';
-GET_DATA
-TYPE &COUNTRY_OUT
-EXIT

Tom,

I tried this method. It gives me the row values in the continuous format separated by comma. But also has other values/text as shown below. Also the number 000039 is appearing in front of all values. I just want the data like so.
dynamic text place holder for graph J03, dynamic text place holder for graph J06, dynamic text place holder for graph J07

Can you suggest the changes?

 Your request did not return any output to display.
Possible causes:
 - No data rows matched the specified selection criteria.
 - Output was directed to a destination such as a file or printer.
 - An error occurred during the parsing or running of the request. 


--------------------------------------------------------------------------------

 0 NUMBER OF RECORDS IN TABLE=        3  LINES=      3
 000039dynamic text place holder for graph J03,000039dynamic text place holder for graph J06,000039dynamic text place holder for graph J07

October 02, 2017, 01:42 PM

MartinY

quote:

Also the number 000039 is appearing in front of all values

Sandeep,

Does the column that you used (the COUNTRY field used by Tom in his sample) is a variable length field (something such as A39V) ?

Tom sample code will work properly with fix length field (A39). The 000039 is the length of the field used on your side.

If you use Tom's sample as below, you will see a 000010 in front of each value:

TABLE FILE CAR
PRINT
     COUNTRY/A10V
  BY COUNTRY NOPRINT
 ON TABLE HOLD AS HOLD1 FORMAT ALPHA
END
-RUN
-SET &XLINES = &LINES;
-SET &CNTR = 0;
-SET &COUNTRY_OUT = '';
-SET &COUNTRY     = '';
-REPEAT GET_DATA &XLINES TIMES
-READ HOLD1 NOCLOSE &COUNTRY.A10.
-SET &CNTR = &CNTR + 1;
-SET &COUNTRY_OUT = IF &CNTR EQ 1 THEN &COUNTRY || ',' ELSE 
-                   IF &CNTR EQ &XLINES THEN &COUNTRY_OUT || &COUNTRY ELSE &COUNTRY_OUT || &COUNTRY || ',';
-GET_DATA
-TYPE &COUNTRY_OUT
-EXIT

October 02, 2017, 02:19 PM

Sandeep

quote:
Originally posted by MartinY:
quote:

Also the number 000039 is appearing in front of all values

Sandeep,

Does the column that you used (the COUNTRY field used by Tom in his sample) is a variable length field (something such as A39V) ?

Tom sample code will work properly with fix length field (A39). The 000039 is the length of the field used on your side.

If you use Tom's sample as below, you will see a 000010 in front of each value:
TABLE FILE CAR
PRINT
     COUNTRY/A10V
  BY COUNTRY NOPRINT
 ON TABLE HOLD AS HOLD1 FORMAT ALPHA
END
-RUN
-SET &XLINES = &LINES;
-SET &CNTR = 0;
-SET &COUNTRY_OUT = '';
-SET &COUNTRY     = '';
-REPEAT GET_DATA &XLINES TIMES
-READ HOLD1 NOCLOSE &COUNTRY.A10.
-SET &CNTR = &CNTR + 1;
-SET &COUNTRY_OUT = IF &CNTR EQ 1 THEN &COUNTRY || ',' ELSE 
-                   IF &CNTR EQ &XLINES THEN &COUNTRY_OUT || &COUNTRY ELSE &COUNTRY_OUT || &COUNTRY || ',';
-GET_DATA
-TYPE &COUNTRY_OUT
-EXIT

Martin,

the column I am using is A256V in the mfd. I checked the length of the current value in the column. It is 39. Some how that's value that is being written in the output. Not sure why. Can you suggest what changes to make to get the desired output? Thank you.

October 02, 2017, 03:02 PM

MartinY

quote:
the column I am using is A256V in the mfd

If you are using a variable column length (A256V), Tom's technique will display the field length as the first characters before the text value in the output.

The column as to be a fixed value to avoid the display of its length in the result.

Or another solution is to use READFILE instead of READ such as below.
In my sample I change the format of COUNTRY from a fix length of A10 to variable length of A10V (just for purpose of the sample) but the result is properly displayed using READFILE

TABLE FILE CAR
PRINT COUNTRY/A10V
BY COUNTRY NOPRINT
ON TABLE HOLD AS HOLD1
END
-RUN
-SET &XLINES = &LINES;
-SET &CNTR = 0;
-SET &COUNTRY_OUT = '';
-SET &COUNTRY     = '';
-REPEAT GET_DATA &XLINES TIMES
-READFILE HOLD1
-SET &CNTR = &CNTR + 1;
-SET &COUNTRY_OUT = IF &CNTR EQ 1 THEN &COUNTRY || ',' ELSE 
-                   IF &CNTR EQ &XLINES THEN &COUNTRY_OUT || &COUNTRY ELSE &COUNTRY_OUT || &COUNTRY || ',';
-GET_DATA
-TYPE &COUNTRY_OUT
-EXIT

October 02, 2017, 03:05 PM

Hallway

This is the method that I adopted from @TonyA

  
-DEFAULTH &VAL_STR = ''

TABLE FILE CAR
  SUM COMPUTE CNTR = LAST CNTR + 1; NOPRINT
      COMPUTE COMMA/A1 = IF CNTR EQ 1 THEN '' ELSE ','; NOPRINT
      COMPUTE VAL_STR/A100V = LAST VAL_STR || COMMA || LJUST(12, COUNTRY, 'A12V');
   BY COUNTRY NOPRINT
ON TABLE HOLD AS TEMPHLD1
END
-RUN

TABLE FILE TEMPHLD1
  SUM LST.VAL_STR
ON TABLE HOLD AS TEMPHLD2
END
-RUN

-READFILE TEMPHLD2
-SET VAL_STR = TRUNCATE(&VAL_STR);
-TYPE &|VAL_STR = &VAL_STR

October 02, 2017, 03:12 PM

MartinY

Hallway,

The secret is the use of READFILE instead of READ
If you perform the below (your sample with a READ instead of READFILE), your result will show the 000039 at the beginning
Also, no need to specify the TEMPHLD2 format with a READFILE.


-DEFAULTH &VAL_STR = ''

TABLE FILE CAR
  SUM COMPUTE CNTR = LAST CNTR + 1; NOPRINT
      COMPUTE COMMA/A1 = IF CNTR EQ 1 THEN '' ELSE ','; NOPRINT
      COMPUTE VAL_STR/A100V = LAST VAL_STR || COMMA || LJUST(12, COUNTRY, 'A12V');
   BY COUNTRY NOPRINT
ON TABLE HOLD AS TEMPHLD1
END
-RUN

TABLE FILE TEMPHLD1
  SUM LST.VAL_STR
ON TABLE HOLD AS TEMPHLD2 FORMAT ALPHA
END
-RUN

-READ TEMPHLD2 &VAL_STR.A100
-SET &VAL_STR = TRUNCATE(&VAL_STR);
-TYPE &|VAL_STR = &VAL_STR

October 02, 2017, 04:45 PM

Sandeep

Martin, Hallway,

I tried both of your versions. Martin's code does not give me any output. Hallway's code shows the values but still have the format length in front of it. I have pasted both the codes below and also the output.

Martin's Code

 
TABLE FILE MFD
PRINT
     GRAPH_COMMENTS/A256V
  BY GRAPH_COMMENTS NOPRINT
WHERE ( MFD.MFD.DF_AGENCY EQ &CF_AGENCY.(OR(FIND DF_AGENCY,MFD.MFD.DF_AGENCY IN MFD)).CF_AGENCY. ) AND
 ( MFD.MFD.DF_YEAR EQ CURRENTCY_INT ) AND
 ( MFD.MFD.CF_MONTH_INT LE CURRENTAYINT_LATEST ) AND
 ( MFD.MFD.GRAPH_NO EQ '1.1' );
 ON TABLE HOLD AS HOLD1 FORMAT ALPHA
END
-RUN
-SET &XLINES = &LINES;
-SET &CNTR = 0;
-SET &COUNTRY_OUT = '';
-SET &COUNTRY     = '';
-REPEAT GET_DATA &XLINES TIMES
-READFILE HOLD1 
-SET &CNTR = &CNTR + 1;
-SET &COUNTRY_OUT = IF &CNTR EQ 1 THEN &COUNTRY || ',' ELSE 
-                   IF &CNTR EQ &XLINES THEN &COUNTRY_OUT || &COUNTRY ELSE &COUNTRY_OUT || &COUNTRY || ',';
-GET_DATA
-TYPE &COUNTRY_OUT
-*ON TABLE HOLD AS HOLD2 FORMAT FLEX
-EXIT

Output:

 0 NUMBER OF RECORDS IN TABLE=        3  LINES=      3
 ,,

Hallway's Code

 -DEFAULTH &VAL_STR = ''

TABLE FILE MFD
  SUM COMPUTE CNTR = LAST CNTR + 1; NOPRINT
      COMPUTE COMMA/A1 = IF CNTR EQ 1 THEN '' ELSE ','; NOPRINT
      COMPUTE VAL_STR/A256V = LAST VAL_STR || COMMA || LJUST(256, GRAPH_COMMENTS, 'A256V');
   BY GRAPH_COMMENTS NOPRINT
WHERE ( MFD.MFD.DF_AGENCY EQ &CF_AGENCY.(OR(FIND DF_AGENCY,MFD.MFD.DF_AGENCY IN MFD)).CF_AGENCY. ) AND
 ( MFD.MFD.DF_YEAR EQ CURRENTCY_INT ) AND
 ( MFD.MFD.CF_MONTH_INT LE CURRENTAYINT_LATEST ) AND
 ( MFD.MFD.GRAPH_NO EQ '1.1' );   
ON TABLE HOLD AS TEMPHLD1
END
-RUN

TABLE FILE TEMPHLD1
  SUM LST.VAL_STR
ON TABLE HOLD AS TEMPHLD2 FORMAT ALPHA
END
-RUN

-READ TEMPHLD2 &VAL_STR.A256
-SET &VAL_STR = TRUNCATE(&VAL_STR);
-TYPE &|VAL_STR = &VAL_STR

-EXIT

Output:

 0 NUMBER OF RECORDS IN TABLE=        3  LINES=      3
 0 NUMBER OF RECORDS IN TABLE=        3  LINES=      1
 &VAL_STR = 000256dynamic text place holder for graph J03,dynamic text place holder for graph J06,dynamic text place holder for graph J07

The output I am looking for is just the text.

Desired Output:

 dynamic text place holder for graph J03,dynamic text place holder for graph J06,dynamic text place holder for graph J07

Can you please suggest the changes in the code? Thanks.

October 02, 2017, 05:55 PM

FP Mod Chuck

Change this line of code from.


COMPUTE VAL_STR/A256V = LAST VAL_STR || COMMA || LJUST(256, GRAPH_COMMENTS, 'A256V');


COMPUTE VAL_STR/A256 = LAST VAL_STR || COMMA || LJUST(256, GRAPH_COMMENTS, 'A256');

October 03, 2017, 08:32 AM

Frans

or don't use format ALPHA with Hallway's Code:

TABLE FILE TEMPHLD1
SUM LST.VAL_STR
ON TABLE HOLD AS TEMPHLD2
END
-RUN

-READFILE TEMPHLD2
-SET VAL_STR = TRUNCATE(&VAL_STR);
-TYPE &|VAL_STR = &VAL_STR

Alternativly you can use Frans' solution

difference is that this is done in one TABLE request and using comma delimited output to put the comma's:

FILEDEF HOLD DISK hold.csv (LRECL 200 RECFM v
TABLE FILE CAR
SUM MAX.COUNTRY
ACROSS COUNTRY NOPRINT
ON TABLE HOLD FORMAT COM
END
-RUN

-READ HOLD &VAL_STR.A200.
-TYPE &VAL_STR

-*if you don't need the double quotes:
-SET &VAL_STRING_STRIPPED = STRIP(&VAL_STR.LENGTH, &VAL_STR.QUOTEDSTRING, '"', 'A&VAL_STR.LENGTH');
-TYPE &VAL_STRING_STRIPPED
-EXIT

October 03, 2017, 08:36 AM

MartinY

quote:
TABLE FILE MFD
PRINT
GRAPH_COMMENTS/A256V
BY GRAPH_COMMENTS NOPRINT
WHERE ( MFD.MFD.DF_AGENCY EQ &CF_AGENCY.(OR(FIND DF_AGENCY,MFD.MFD.DF_AGENCY IN MFD)).CF_AGENCY. ) AND
( MFD.MFD.DF_YEAR EQ CURRENTCY_INT ) AND
( MFD.MFD.CF_MONTH_INT LE CURRENTAYINT_LATEST ) AND
( MFD.MFD.GRAPH_NO EQ '1.1' );
ON TABLE HOLD AS HOLD1 FORMAT ALPHA
END...

Must be

TABLE FILE MFD
PRINT
     GRAPH_COMMENTS
  BY GRAPH_COMMENTS NOPRINT
WHERE ( MFD.MFD.DF_AGENCY EQ &CF_AGENCY.(OR(FIND DF_AGENCY,MFD.MFD.DF_AGENCY IN MFD)).CF_AGENCY. ) AND
 ( MFD.MFD.DF_YEAR EQ CURRENTCY_INT ) AND
 ( MFD.MFD.CF_MONTH_INT LE CURRENTAYINT_LATEST ) AND
 ( MFD.MFD.GRAPH_NO EQ '1.1' );
 ON TABLE HOLD AS HOLD1
END
...

If GRAPH_COMMENTS is already a fix length don't change it to a variable length. And if it's a variable length, no need to change it again in variable length.
In my sample I have changed the format of COUNTRY to A10V because IB sample files don't have variable length.

With READFILE you don't need to reformat the field at all. READFILE will manage the length.

October 03, 2017, 03:58 PM

Sandeep

quote:

If GRAPH_COMMENTS is already a fix length don't change it to a variable length. And if it's a variable length, no need to change it again in variable length.
In my sample I have changed the format of COUNTRY to A10V because IB sample files don't have variable length.

With READFILE you don't need to reformat the field at all. READFILE will manage the length.

Martin,
The graph_comments column is a variable length column.

I used your original code but it is not retuning any data. It only gives output like I had posted in above reply. No values appearing only the commas. What do you suggest. Also in the response I only need the text as output. How do I get rid of the system generated text. Please see the output I had pasted above. Thank you.

 TABLE FILE MFD
PRINT
     GRAPH_COMMENTS
  BY GRAPH_COMMENTS NOPRINT
WHERE ( MFD.MFD.DF_AGENCY EQ &CF_AGENCY.(OR(FIND DF_AGENCY,MFD.MFD.DF_AGENCY IN MFD)).CF_AGENCY. ) AND
 ( MFD.MFD.DF_YEAR EQ CURRENTCY_INT ) AND
 ( MFD.MFD.CF_MONTH_INT LE CURRENTAYINT_LATEST ) AND
 ( MFD.MFD.GRAPH_NO EQ '1.1' );
 ON TABLE HOLD AS HOLD1 
END
-RUN
-SET &XLINES = &LINES;
-SET &CNTR = 0;
-SET &COUNTRY_OUT = '';
-SET &COUNTRY     = '';
-REPEAT GET_DATA &XLINES TIMES
-READFILE HOLD1 
-SET &CNTR = &CNTR + 1;
-SET &COUNTRY_OUT = IF &CNTR EQ 1 THEN &COUNTRY || ',' ELSE 
-                   IF &CNTR EQ &XLINES THEN &COUNTRY_OUT || &COUNTRY ELSE &COUNTRY_OUT || &COUNTRY || ',';
-GET_DATA
-TYPE &COUNTRY_OUT
-EXIT

October 03, 2017, 04:05 PM

Sandeep

quote:

Alternatively you can use Frans' solution difference is that this is done in one TABLE request and using comma delimited output to put the comma's:

Frans,

I tried your steps. It gives the values but I get it in column format. I needed the values as continuous text separated by comma.
Here is the code I used. How do I change it to only text format.

 FILEDEF HOLD DISK hold.csv
TABLE FILE MFD
SUM
     MAX.MFD.MFD.GRAPH_COMMENTS
ACROSS MFD.MFD.GRAPH_COMMENTS NOPRINT
WHERE ( MFD.MFD.DF_AGENCY EQ &CF_AGENCY.(OR(FIND DF_AGENCY,MFD.MFD.DF_AGENCY IN MFD)).CF_AGENCY. ) AND
 ( MFD.MFD.DF_YEAR EQ CURRENTCY_INT ) AND
 ( MFD.MFD.CF_MONTH_INT LE CURRENTAYINT_LATEST ) AND
 ( MFD.MFD.GRAPH_NO EQ '1.1' );
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE SET HTMLEMBEDIMG ON
ON TABLE SET HTMLCSS ON
END
-RUN

-READ HOLD &VAL_STR.A256.
-TYPE &VAL_STR
-SET &VAL_STRING_STRIPPED = STRIP(&VAL_STR.LENGTH, &VAL_STR.QUOTEDSTRING, '"', 'A&VAL_STR.LENGTH');
-TYPE &VAL_STRING_STRIPPED
-EXIT
ON TABLE SET STYLE *
INCLUDE = IBFS:/EDA/EDA/_EDAHOME/ETC/endeflt.sty,
$
TYPE=REPORT,
     BORDER=OFF,
$

October 03, 2017, 04:07 PM

Tom Flynn

Too many cooks in the kitchen, although, you've been shown the many ways to climb the WebFOCUS mountain...

TABLE FILE MFD
PRINT
     GRAPH_COMMENTS/A256
  BY GRAPH_COMMENTS NOPRINT
WHERE ( MFD.MFD.DF_AGENCY EQ &CF_AGENCY.(OR(FIND DF_AGENCY,MFD.MFD.DF_AGENCY IN MFD)).CF_AGENCY. ) AND
 ( MFD.MFD.DF_YEAR EQ CURRENTCY_INT ) AND
 ( MFD.MFD.CF_MONTH_INT LE CURRENTAYINT_LATEST ) AND
 ( MFD.MFD.GRAPH_NO EQ '1.1' );
 ON TABLE SET HOLDLIST PRINTONLY
 ON TABLE HOLD AS HOLD1 
END
-RUN
-SET &XLINES = &LINES;
-SET &CNTR = 0;
-SET &GRAPH_COMMENTS_OUT = '';
-SET &GRAPH_COMMENTS     = '';
-REPEAT GET_DATA &XLINES TIMES
-READFILE HOLD1 
-SET &CNTR = &CNTR + 1;
-SET &GRAPH_COMMENTS_OUT = IF &CNTR EQ 1 THEN &GRAPH_COMMENTS || ',' ELSE 
-                          IF &CNTR EQ &XLINES THEN &GRAPH_COMMENTS_OUT || &GRAPH_COMMENTS ELSE &GRAPH_COMMENTS_OUT || &GRAPH_COMMENTS  || ',';
-GET_DATA
-TYPE &GRAPH_COMMENTS_OUT
-EXIT

October 04, 2017, 10:37 AM

Sandeep

quote:
Originally posted by Tom Flynn:
Too many cooks in the kitchen, although, you've been shown the many ways to climb the WebFOCUS mountain...

Tom,

I am thankful to all the great cooks for helping me out in preparing the webfocus soufflé

. Have learned a lot. I tried your modified code and I am almost there. The values are coming correctly as continuous text with comma. But the output also comes with the below shown text. How to get rid of it and have just the values? Can you please suggest? Thanks.

 Your request did not return any output to display.
Possible causes:
 - No data rows matched the specified selection criteria.
 - Output was directed to a destination such as a file or printer.
 - An error occurred during the parsing or running of the request. 


--------------------------------------------------------------------------------

 0 NUMBER OF RECORDS IN TABLE=        3  LINES=      3

October 04, 2017, 10:47 AM

Frans

quote:
Originally posted by Sandeep:
quote:

Alternatively you can use Frans' solution difference is that this is done in one TABLE request and using comma delimited output to put the comma's:

Frans,

I tried your steps. It gives the values but I get it in column format. I needed the values as continuous text separated by comma.
Here is the code I used. How do I change it to only text format.

Because you used it a bit differently, here is how it works:

 FILEDEF HOLD DISK hold.csv (LRECL 200 RECFM v
TABLE FILE MFD
SUM
     MAX.MFD.MFD.GRAPH_COMMENTS
ACROSS MFD.MFD.GRAPH_COMMENTS NOPRINT
WHERE ( MFD.MFD.DF_AGENCY EQ &CF_AGENCY.(OR(FIND DF_AGENCY,MFD.MFD.DF_AGENCY IN MFD)).CF_AGENCY. ) AND
 ( MFD.MFD.DF_YEAR EQ CURRENTCY_INT ) AND
 ( MFD.MFD.CF_MONTH_INT LE CURRENTAYINT_LATEST ) AND
 ( MFD.MFD.GRAPH_NO EQ '1.1' );
ON TABLE HOLD FORMAT COM
END
-RUN

-READ HOLD &VAL_STR.A256.
-TYPE &VAL_STR
-SET &VAL_STRING_STRIPPED = STRIP(&VAL_STR.LENGTH, &VAL_STR.QUOTEDSTRING, '"', 'A&VAL_STR.LENGTH');
-TYPE &VAL_STRING_STRIPPED
-EXIT

October 04, 2017, 10:50 AM

MartinY

quote:

Your request did not return any output to display.
Possible causes:
- No data rows matched the specified selection criteria.
- Output was directed to a destination such as a file or printer.
- An error occurred during the parsing or running of the request.

--------------------------------------------------------------------------------

0 NUMBER OF RECORDS IN TABLE= 3 LINES= 3

As far as I know, the above is not an error. It just telling you that you are not processing any displayable report since in your case, reason #2 is the cause.
It is just an warning.

October 04, 2017, 10:59 AM

Tom Flynn

You now have the data in an ampere variable - &GRAPH_COMMENTS_OUT
Do with it what you will...

You have no output, hence, the message...

October 04, 2017, 11:02 AM

Sandeep

Martin,

I agree, this is not an error. I assume it shows when the output is not coming from TABLE or a GRAPH. How do i get the values from &VAL_STRING_STRIPPED only?

October 04, 2017, 11:03 AM

Sandeep

quote:
Originally posted by Tom Flynn:
You now have the data in an ampere variable - &GRAPH_COMMENTS_OUT
Do with it what you will...

Place this at the top of the program:
SET MSG = OFF

Tom,

Just saw your message. Let me try. Will update.

October 04, 2017, 11:06 AM

Tom Flynn

Doesn't stop the message, you have no output. Continue with the program and use the variable for whatever you're doing...

Good Luck!

October 04, 2017, 11:33 AM

Sandeep

Thank you Everyone for the help. I got the output I was looking for. Here is the code just in case someone else runs in to the same requirement.

 TABLE FILE MFD
PRINT
     GRAPH_COMMENTS/A256
  BY GRAPH_COMMENTS NOPRINT
WHERE ( MFD.MFD.DF_AGENCY EQ &CF_AGENCY.(OR(FIND DF_AGENCY,MFD.MFD.DF_AGENCY IN MFD)).CF_AGENCY. ) AND
 ( MFD.MFD.DF_YEAR EQ CURRENTCY_INT ) AND
 ( MFD.MFD.CF_MONTH_INT LE CURRENTAYINT_LATEST ) AND
 ( MFD.MFD.GRAPH_NO EQ '1.1' );
 ON TABLE SET HOLDLIST PRINTONLY
 ON TABLE HOLD AS HOLD1 
END
-RUN
-SET &XLINES = &LINES;
-SET &CNTR = 0;
-SET &GRAPH_COMMENTS_OUT = '';
-SET &GRAPH_COMMENTS     = '';
-REPEAT GET_DATA &XLINES TIMES
-READFILE HOLD1 
-SET &CNTR = &CNTR + 1;
-SET &GRAPH_COMMENTS_OUT = IF &CNTR EQ 1 THEN &GRAPH_COMMENTS || ',' ELSE 
-                          IF &CNTR EQ &XLINES THEN &GRAPH_COMMENTS_OUT || &GRAPH_COMMENTS ELSE &GRAPH_COMMENTS_OUT || &GRAPH_COMMENTS  || ',';
-GET_DATA
-TYPE &GRAPH_COMMENTS_OUT
-EXITTABLE FILE MFD
PRINT
     GRAPH_COMMENTS/A256
  BY GRAPH_COMMENTS NOPRINT
WHERE ( MFD.MFD.DF_AGENCY EQ &CF_AGENCY.(OR(FIND DF_AGENCY,MFD.MFD.DF_AGENCY IN MFD)).CF_AGENCY. ) AND
 ( MFD.MFD.DF_YEAR EQ CURRENTCY_INT ) AND
 ( MFD.MFD.CF_MONTH_INT LE CURRENTAYINT_LATEST ) AND
 ( MFD.MFD.GRAPH_NO EQ '1.1' );
 ON TABLE SET HOLDLIST PRINTONLY
 ON TABLE HOLD AS HOLD1 FORMAT ALPHA
END
-RUN
-SET &XLINES = &LINES;
-SET &CNTR = 0;
-SET &GRAPH_COMMENTS_OUT = '';
-SET &GRAPH_COMMENTS     = '';
-REPEAT GET_DATA &XLINES TIMES
-READFILE HOLD1
-SET &CNTR = &CNTR + 1;
-SET &GRAPH_COMMENTS_OUT = IF &CNTR EQ 1 THEN &GRAPH_COMMENTS || ',' ELSE
-                          IF &CNTR EQ &XLINES THEN &GRAPH_COMMENTS_OUT || &GRAPH_COMMENTS ELSE &GRAPH_COMMENTS_OUT || &GRAPH_COMMENTS  || ',';
-GET_DATA
-TYPE &GRAPH_COMMENTS_OUT


TABLE FILE MFD
PRINT
GRAPH_COMMENTS NOPRINT
ON TABLE SUBHEAD
"&GRAPH_COMMENTS_OUT"
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = IBFS:/EDA/EDA/_EDAHOME/ETC/endeflt.sty,
$
TYPE=TABHEADING,
     LINE=1,
     JUSTIFY=LEFT,
$
TYPE=TABHEADING,
     LINE=1,
     OBJECT=TEXT,
     ITEM=1,
     FONT='CALIBRI',
     SIZE=11,
     COLOR=RGB(157 34 53),
     STYLE=BOLD,
$
END
-RUN