Hello,

Fairly new to WF and have been searching for help on this problem but haven't come up with a solution, so hoping someone here can offer a suggestion or two. I have a project in which I have to loop through a list of institutions and get data for those institutions and combine them all into one tab delimited file. I have the procedure currently working to loop through and create one tab delimited file per institution, but since there isn't an easy way in Windows to combine those files automatically I'd rather just try and get WF to process one file with all data in it. Is there a way to just append to a tab delimited file while looping through the institutions? Here is the meat of the current code I have running to spit out the multiple files:

TABLE FILE HOLDLIBRARIES
PRINT HOLDLIBRARIES.HOLDLIBRARIES.INSTITUTIONSHORTNAME
ON TABLE HOLD AS HOLDLIST FORMAT ALPHA
END
-RUN

-SET &HOWMANY = &LINES ;
-IF &LINES EQ 0 GOTO EXIT;
-REPEAT ENDLOOP FOR &CTR FROM 1 TO &HOWMANY
-READ HOLDLIST NOCLOSE &INSTITUTIONSHORT.A3.

APP PATH &INSTITUTIONSHORT


TABLE FILE RR197_002
PRINT
J14.RR197_002.AACCT AS 'Account_Id'
PROSPECT_ID AS 'Prospect_Id'
PRIORITY_CODE AS 'Priority_Code'
WHERE ( RR197_002.RR197_002.DMSTAT IN ('4','8') );
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE SAVE FILENAME
'\\filepath\Temp\RR197_&INSTITUTIONSHORT.._&YYMD...txt'
FORMAT TAB
ON TABLE SET HTMLCSS ON

-ENDLOOP
-ENDREPEAT

We are using Dev Studio with v8105.

Thanks for any and all suggestions.

-Joe

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

Which files do you need to append?

Your code will create &HOWMANY files with the same content.

To append your files you can use MATCH.

Hello, thanks for the response Ricardo. The loop is currently producing 9 text files and I need to combine them all into one. After your post I did some quick research on MATCH and I'm unsure of how I would implement that to combine the 9 files. Do you have a good example?

Thanks

Upon further research on MATCH, it looks as though some sort of ID is necessary for the MATCH to combine everything. In my case there is no unique identifier in the 9 files to combine them together so I'm guessing MATCH isn't the answer.

Joe

Did you posted your entire code?

I cant see where you filter any data from each institution. Could you show me please?

MATCH needs at least ONE sort field to work.

Example:

MATCH FILE HLAST1
SUM
TOT_ACU_LAST_YEAR
ACU_LAST_YEAR
BY MANUFACTURER
BY SEGMENTO
RUN
FILE HZ1
SUM SRSD83_RETAIL_SALES_Q
BY MANUFACTURER
BY SEGMENTO
BY VENTAS_MANUF
BY VENTAS_TOTAL
BY MONTH
BY MONTH_REPORT
AFTER MATCH HOLD AS HZ2 OLD-OR-NEW
END

If you create 9 files from the same procedure/query you *MIGHT* be able to use MORE to merge them.

Example:

-* MERGE FILES by MartinY
TABLE FILE HOLD1
SUM FIELD_Q
BY SORT_A
BY SORT_B
BY SORT_C
BY FIELD_X
MORE
FILE HOLD2
MORE
FILE HOLD3
END
-RUN
  

You might look into using MODIFY. I am not an expert at using it, but it has been able to get me what I needed.

I usually hold the master table I want information appended to as a FOCUS table and then hold subsequent tables that will be appended as just the default HOLD format. After the information has been appended to the FOCUS file, you could then write it back out to a comma delimited file.

Hello Ricardo,

The only thing I left out of the code I provided were the joins and the column formatting. Everything else is there. I'll do some more research on MORE, MATCH, and MODIFY to see if any of them fit the bill.

Thanks,
Joe

I would normally just FILEDEF the file to APPEND MODE, then all SAVEs will get appended to the file.

This what I do:

 APP PATH ibisamp
-RUN
-SET &HOLD=HOLD;
-REPEAT ENDLOOP 2 TIMES
 TABLE FILE CAR
 PRINT COUNTRY CAR MODEL SEG.BODYTYPE
 ON TABLE &HOLD AS HOLD1 FORMAT TAB
 END
 FILEDEF HOLD1 DISK hold1.tab ( LRECL 108 RECFM V APPEND
-RUN
-SET &HOLD=SAVE;
-ENDLOOP
-UNIX mv hold1.* &APPROOT/baseapp

I will run the code through 1 loop with a ? FILEDEF so I can see the LRECL of HOLD1.

This assumes that you don't need column heading in the first row. If you do, then do the following:

 APP PATH ibisamp
-RUN
-SET &HOLD=HOLD;
-SET &TAB=TABT;
-REPEAT ENDLOOP 2 TIMES
 TABLE FILE CAR
 PRINT COUNTRY CAR MODEL SEG.BODYTYPE
 ON TABLE &HOLD AS HOLD1 FORMAT &TAB
 END
 FILEDEF HOLD1 DISK hold1.tab ( LRECL 108 RECFM V APPEND
-RUN
-SET &HOLD=SAVE;
-SET &TAB=TAB;
-ENDLOOP
-UNIX mv hold1.* &APPROOT/baseapp

Thank you jgelona and waz! Using the FILEDEF in the manner you suggested worked and I now have one file with all the data.

Thanks everyone else for their suggestions, they will also help me in my journey to learn this product.

-Joe

Joe,

Please edit your first post in this thread and add [SOLVED] or [CLOSED] to the title.