Focal Point
[SOLVED] MERGE HOLD FILES USING LOOP

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/6777014776

July 24, 2015, 01:47 PM
subbu
[SOLVED] MERGE HOLD FILES USING LOOP
hello,

am trying to merge two hold files using loop unfortunately it is not taking the MORE and FILE command. the file name I get from a filename

here is the dummy code I tired. Please let me know if there is anyway to get this.
 
TABLE FILE GETFILES
PRINT FILENAME
ON TABLE SAVE AS FLNAME
END

-RUN
-SET &CNT=1;
-READ FLNAME &FN.&CNT.A10.
-SET &CNT=&CNT+1;

TABLE FILE &FN.&CNT
PRINT *
ON TABLE HOLD AS FINALHLD
-REPEATE LO WHILE &CNT LE &LINES
MORE
FILE &FN.&CNT
END
-LO
END
 


if I do it normal way it would be as below for example. The below is the normal way of merging the hold file I wanted to have a loop to do that. Please let me know if there is a way to bring this.

 
TABLE FILE FEBFILE
PRINT *
ON TABLE HOLD AS FINALHLD
MORE 
FILE MARFILE
MORE
FILE APRFILE
END
 

This message has been edited. Last edited by: <Kathryn Henning>,


WebFOCUS 7.6.10
Windows
Output: Excel,PDF
July 24, 2015, 04:15 PM
John_Edwards
Look up FILEDEF.

  FILEDEF ddname DISK filename.ftm (APPEND 


That APPEND on the end of the command allows you to add records to the end of the existing ftm file (hold file) instead of overwriting. At that point your loop would simply go through each of your filenames, writing to the same ddname.

J.



July 24, 2015, 04:22 PM
John_Edwards
Have a look at this thread for some good examples, and another alternative.

http://forums.informationbuild...1057331/m/3721007892



July 27, 2015, 05:19 AM
Darryl_uk
Am not an expert on loops (as I have one way I always structure them and stick with it!) - am I right in thinking you are not varying the value of &cnt within your loop - so you will enter the loop with one value of &cnt and stay in it without any way out? Am sure you are wanting to start with &cnt at a value of 1 and work up to the value held in &lines and I don't think you are doing that..


7.7.05 Windows.
July 27, 2015, 05:24 AM
Avinash
Hi Subbu,

please try below code, May be this can solve your problem. But for this code, you have to maintain the same structure of hold files.
-------------------------
-*****************************************************************************
-* SET VARIABLE
APP HOLD ibisamp
-SET &ECHO = ALL;
-*-----------------------------------------------------------------------------
-***************Creating hold file which contains the table name***************
-******************************************************************************
TABLE FILE GETFILES
PRINT
TABLE_NAME
ON TABLE HOLD AS FLNAME FORMAT ALPHA
END
-*-----------------------------------------------------------------------------
-********Reading The Table Names and Dyanamic Holdfile Creation****************
-******************************************************************************
-TYPE &LINES
-SET &COUNTER=&LINES;
-SET &A_CNT=1;
-REPEAT LOOPING &COUNTER TIMES
-READ FLNAME &TABLE_NAME.A25
-SET &TABLE_NAME=EDIT(&TABLE_NAME,'$$$$$$999999999');
-TYPE &TABLE_NAME
-SET &FILENAM=HOLDFILE||&A_CNT
TABLE FILE &TABLE_NAME.EVAL
PRINT DIV DEPT
ON TABLE HOLD AS &FILENAM
END
-SET &A_CNT = &A_CNT + 1;
-LOOPING

-*-----------------------------------------------------------------------------
-********Adding Hold File Using More Command***********************************
-******************************************************************************

-TYPE &A_CNT 'COUNTER'
-*-SET &F_CNT=CSAHOLD||&A_CNT;
-SET &COL_CNT=&A_CNT-2;
-SET &A_CNT=2;
TABLE FILE HOLDFILE1
PRINT *
ON TABLE HOLD AS FINALHOLD
-REPEAT LOOPING1 &COL_CNT TIMES
-SET &FIL_NAME=HOLDFILE||&A_CNT;
MORE
FILE &FIL_NAME
-SET &A_CNT = &A_CNT + 1;
-LOOPING1
END

-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
-*-*Creating Report
-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
-*
TABLE FILE FINALHOLD
PRINT
*
END

_________________________________


Thanks!
@vi

WebFOCUS 8105, Dev Studio 8105, Windows 7, ALL Outputs
July 27, 2015, 05:47 AM
Tony A
Where do we start? Wink When you are building code using DM, try using -SET &ECHO=ON; to trap the code that you are creating. You should then see why your code doesn't work!!

Next, your code is not structured correctly.

-SET &CNT=1;
-READ FLNAME &FN.&CNT.A10.
-SET &CNT=&CNT+1; -* This needs to be within the loop to be effective

TABLE FILE &FN.&CNT -* The first point is not needed. Better to code it as &FN&CNT.EVAL
PRINT *
ON TABLE HOLD AS FINALHLD
-REPEATE LO WHILE &CNT LE &LINES;
MORE
FILE &FN.&CNT.EVAL
-* You need to repeat the -READ statement here to get the next filename
END -* This causes incorrect syntax to be issued
-LO
END

My tuppence worth? I would be inclined to use the newer -READFILE syntax.

There is no substitute for using standard debug options such as -SET &ECHO and checking that your syntax is correct. You knew what you needed from hand coding it, so why do you not check the syntax created?


T

This message has been edited. Last edited by: Tony A,



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
July 27, 2015, 06:00 AM
Tony A
Avinash
quote:
-REPEAT LOOPING1 &COL_CNT TIMES

This is incorrect syntax, you need the trailing semicolon.

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
July 27, 2015, 06:20 AM
Tony A
Example using -READFILE without the unnecessary &CNT!

FILEDEF FINALHLD DISK FINALHLD.FTM (APPEND
-RUN

-READFILE GETFILES
TABLE FILE &FILENAME
PRINT *
ON TABLE HOLD AS FINALHLD

-REPEAT :Loop2 WHILE &IORETURN EQ 0;
-READFILE GETFILES
-IF &IORETURN NE 0 THEN GOTO :Loop2;
MORE
FILE &FILENAME
-:Loop2
END

TABLE FILE FINALHLD
PRINT *
END

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
July 27, 2015, 08:23 AM
Avinash
Thanks Tony for your feedback. your code is very nice. But as per Subbu's query what i understand, Subbu has a list of table names in an other table and need to read the table name value in that table for creating the hold file dynamically.

Please suggest me if im wrong. Smiler


Thanks!
@vi

WebFOCUS 8105, Dev Studio 8105, Windows 7, ALL Outputs
July 27, 2015, 09:07 AM
Tony A
Hi Avinash,

That's exactly what that code does.

If you want a fuller example using a sample database (GGSALES), then try this -
-DEFAULTH &FILENAME = '', &MNTHYEAR = ''
DEFINE FILE GGSALES
  YEAR/YY = DATE;
  MNTH/MT = DATE;
  MNTHYEAR/MTYY = DATE;
  FILENAME/A10 = EDIT(FPRINT(MNTHYEAR, 'MTYY', 'A10'), '999FILE');
END

TABLE FILE GGSALES
  SUM MNTHYEAR
   BY MNTHYEAR NOPRINT
   BY FILENAME
WHERE YEAR EQ 1996;
WHERE MNTH FROM 2 TO 6;
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS GETFILES
END
-RUN

-READFILE GETFILES
-REPEAT :Loop WHILE &IORETURN EQ 0;
-TYPE &|FILENAME IS &FILENAME
TABLE FILE GGSALES
  SUM DOLLARS
      FILENAME
   BY REGION
   BY ST
   BY CITY
WHERE MNTHYEAR EQ &MNTHYEAR
ON TABLE HOLD AS &FILENAME
END
-RUN
-READFILE GETFILES

-:Loop

FILEDEF FINALHLD DISK FINALHLD.FTM (APPEND
-RUN

-READFILE GETFILES
TABLE FILE &FILENAME
PRINT *
ON TABLE HOLD AS FINALHLD

-REPEAT :Loop2 WHILE &IORETURN EQ 0;
-READFILE GETFILES
-IF &IORETURN NE 0 THEN GOTO :Loop2;
MORE
FILE &FILENAME
-:Loop2
END

TABLE FILE FINALHLD
PRINT *
END


T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
July 27, 2015, 09:14 AM
j.gross
quote:
When using subscripts, you need to use the .EVAL extension.


I think you mean "... you should not use .EVAL"

Consider, for &CNT = 2:

-READ FLNAME &FN.&CNT.A10. /* reads a filename value into &FN2

TABLE FILE &FN.&CNT /* equivalent to &FN2, which evaluates to the second filename

FILE &FN.&CNT.EVAL /* equivalent to &FN.2 which probably generates an error, since plain &FN is unassigned


Then again, unless there is need to preserve the series of values for later re-use, why not just use (and re-euse) plain &FN to hold the successive filenames?


- Jack Gross
WF through 8.1.05
July 27, 2015, 09:45 AM
Tony A
quote:
why not just use (and re-euse) plain &FN to hold the successive filenames?

... which is exactly what I suggested in the example.

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
July 27, 2015, 10:03 AM
j.gross
Tony --

Indeed you did. Missed that post while I was editing -- I was commenting on your first post.
July 27, 2015, 10:08 AM
Avinash
quote:
Originally posted by Tony A:
That's exactly what that code does.
T


Thanks Tony for clarification.. Smiler

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


Thanks!
@vi

WebFOCUS 8105, Dev Studio 8105, Windows 7, ALL Outputs
July 28, 2015, 01:27 PM
subbu
Hello All,


Thanks for all(John, Tony,Avinash) your solutions given. I have completed my task with Filedef however I learnt new things.

Tony - Thanks for pointing the unstructured code, like I said it is dummy code i.e.. I just put for anyone just reference what I'm looking for. Thank you for all your comments or suggestions.


I appreciate you all!!

-Subbu


WebFOCUS 7.6.10
Windows
Output: Excel,PDF