Hi,
We have csv file which is created every monday by batch jobs using FOCUS and is ftped to server for users to analyse. Most of the weeks we have rows less than 65000(approx limit supported by microsoft excel). But sometimes it goes above this max limit and users are not able to get the complete data in excel. I rerun the FOCUS program and create two output files(one for 3 days and other for 2 days, instead of one file for full week). To solve this I tried using RECORDLIMIT but it supports LE and EQ only.
My Aim is -

IF RECORDLIMIT LE 65000
.......
ON TABLE HOLD AS FILE1 FORMAT LOTUS

and

IF RECORDLIMIT GT 65000
.......
ON TABLE HOLD AS FILE2 FORMAT LOTUS

But FOCUS 7.1.1 does not allow second syntax.

Any idea how to solve this?

Thanks,
Gaurav.

I think you would have to DEFINE a field that is a counter, and then filter on that field...

DEFINE FILE HOLD01
COUNTER/P8 = COUNTER = COUNTER + 1;
END

TABLE FILE HOLD01
PRINT
...
WHERE COUNTER LE 65000
ON TABLE HOLD AS ...
END

TABLE FILE HOLD01
PRINT
...
WHERE COUNTER GT 65000
WHERE COUNTER LE 130000
ON TABLE HOLD AS ...
END

I take it the issue is the 7.1.1 version.

Have you tried FORMAT CSV, it creates a comma delimited file as well.

Just ignore me, it might help if I read the 'whole' thing.

The define counter option suggested should work.

gp, RECORDLIMIT is only really meaningful as
IF RECORDLIMIT EQ ....
Recordlimit isn't a variable, so you cant test on it the way you can test other vars. so the define counter suggestion actually creates a variable that you can test on..which is just what you want here.

Hi all,
Thanks for your quick replies. But it seems I am missing something. My code is,

DEFINE FILE HOLD1 ADD
COUNTER/P8 WITH CUS=COUNTER=COUNTER+1;
END

TABLE FILE HOLD1
IF COUNTER LE 65000
PRINT
COUNTER
....
ON TABLE HOLD AS REPORT1 FORMAT LOTUS
END
TABLE FILE HOLD1
IF COUNTER GT 65000
PRINT
COUNTER
....
ON TABLE HOLD AS REPORT2 FORMAT LOTUS
END

All the rows are getting written in first file only because counter is always 0. Why is not getting incremented? Am I doing anything wrong?

GP.

Try:
DEFINE FILE HOLD1 ADD
COUNTER/P8 WITH CUS=LAST COUNTER+1;
END

Yup - adding last did the magic.
Thanks for all your help !!!

Hi GP,

Here's an example that creates one or more hold files depending on how many records are in the original file and how many records you want in each hold file. In this example I'm extracting 275 records from "BIGFILE" and creating hold files of 100 records each. The result will be three files:
file01.prn - 100 records
file02.prn - 100 records
file03.prn - 75 records

DEFINE FILE BIGFILE
CNTR/I7 = CNTR + 1;
REC_CNT/I5 = IF REC_CNT LT 100 THEN REC_CNT + 1 ELSE 1;
FIL_CNT/I2 = IF REC_CNT EQ 1 THEN FIL_CNT + 1 ELSE FIL_CNT;
END
TABLE FILE BIGFILE
PRINT FIL_CNT REC_CNT CNTR FIELD1 FIELD2
WHERE RECORDLIMIT EQ 275
ON TABLE HOLD AS HOLD_BF
END
-*
DEFINE FILE HOLD_BF
FIL/D2L = FIL_CNT;
CNT/I5 = 1;
END
TABLE FILE HOLD_BF
SUM CNT
BY FIL
ON TABLE SAVE AS LOOPCTRL
END
-RUN
-*
-SET &V_FIL = ' ';
-SET &V_CNT = ' ';
-LOOPER
-READ LOOPCTRL NOCLOSE &V_FIL &V_CNT
-IF &IORETURN NE 0 GOTO EOF;
-SET &OUTFILE = 'FILE' || &V_FIL;
-*
TABLE FILE HOLD_BF
PRINT FIL_CNT REC_CNT CNTR FIELD1 FIELD2
WHERE FIL_CNT EQ &V_FIL
ON TABLE HOLD AS &OUTFILE FORMAT LOTUS
END
-RUN
-GOTO LOOPER
-EOF
-CLOSE LOOPCTRL

I don't know if you have any interest in this, but if so, give it a try.

Jim

WebFOCUS 5.3 New Feature Excel Worksheets.

Excel Compound Reports

The compound report has been extended to support Excel. WebFOCUS can now generate
Excel reports that contain multiple worksheets in a single workbook. This new feature is
based on the existing PDF compound report model and offers three different scenarios.

� Multiple reports can be merged into a single Excel workbook with each report residing
in its own uniquely named worksheet.

� Multiple reports can be merged into a single Excel workbook with all reports flowing
continually in a single named worksheet.

� A single report can be burst into multiple worksheets within a single workbook based
on a sort value in the report.

I would generate a seperate worksheet for some by field (burst) as noted in bullet # 3