Greetings Intelligencia!

Once again I am asking for your guidance! Recently, you solved my problems regarding the use of input fields compared against table columns to derive a total (see CREATING VALUES BASED ON AMPER VARIABLES). I was off to the races and successfully generated 11 beautiful TABLE SUBTOTALs which showed accurate totals by MONTH/YEAR, within the INPUT DATE RANGE, and based on various other criteria. Thanks!

Now my challenge is combining the counts from these 11 tables into one print line on my report. I changed the 'ON TABLE SUBTOTAL' lines to 'ON TABLE HOLD AS temptblname', added a MYYTtl# field on all tables to use as a common JOIN field, JOINed the tables on MYYTtl#, DEFINEd a final TABLE FILE and tried PRINTing my counts. The first table's data showed up in the first column with all MONTH/YEAR total rows correct, but subsequent tables just repeated the first column's value in each row all the way down. I thought it was because of the field I used to JOIN so I tried to figure out how to use each different MONTH/YEAR or the &Cntr as a JOIN field but with no luck. Here is sample code from the HOLD tables I created:

***********************
-* Start File nxtwkco.fex
DEFINE FILE TBLBEREAVEMENTBURIAL
DATEYYM3/YM =
IF DATERECEIVED GT DT(18000101000000) THEN
HDATE(DATERECEIVED, 'YYMD')
ELSE
HDATE(DATEADDED, 'YYMD');
MYYTtl3 = AYM(&FROM_YRMTH, 0, 'I6') ;
END

TABLE FILE TBLBEREAVEMENTBURIAL
COUNT
DATEYYM3 AS 'Count'
BY MYYTtl3
BY DATEYYM3
-SET &MnthCount = YM(&FROM_YRMTH,&TO_YRMTH,'I3') ;
-SET &Rows = 'ROWS' ;

-REPEAT DATELOOP3 FOR &Cntr FROM 0 TO &MnthCount ;
-SET &CnYrMth = AYM(&FROM_YRMTH, &Cntr , 'I6') ;
-SET &DtDesc= CHGDAT('YYM', 'YYXM', &CnYrMth , 'A17');
&Rows &CnYrMth AS '&DtDesc'
-SET &Rows = 'OVER' ;
-DATELOOP3

WHERE DATEBURIAL IS MISSING
AND DATECLOSED IS MISSING
AND DATEYYM3 FROM &FROM_YRMTH TO &TO_YRMTH
ON TABLE SUBTOTAL
-*ON TABLE HOLD AS nxtwkco
END
***********************


And here is my JOIN code that printed the first table correctly and subsequent tables with just the first value repeated all the way down.

+++++++++++++++++++++++
.
.
JOIN MYYTtl IN lastwkco TO MYYTtl3 IN nxtwkco AS J3 END
.
.
.
DEFINE FILE lastwkco
END
TABLE FILE lastwkco
PRINT DATEYYM DATEYYM2 DATEYYM3
END
+++++++++++++++++++++++

Please give me a hint as to what I am doing wrong.

Eternally in your debt!
Sam

Samf,
are you trying to print one line with all the totals running across ? How many totals are you printing?

Hello RSquared,

I am trying to print 1 line across for each MONTH/YEAR with 11 different totals displaying for each MONTH/YEAR.

Thanks!
Sam

If each of the 11 tables is sorted by month and year with 1 total each, I would do a MATCH FILE. The match would essentially concatenate the lines together.

Samf,

You can also do -SET for each Total Amount at the end of each report and then print them using the OVER command for each line.

Hey Ginny and RSquared,

At the bottom I've included sample output from 2 of the 11 tables for your viewing pleasure!

My final report will look something like this:

++++++++++++++++++++++++++++++++++++++++++++++

...............Last Month C/Os...New Referrals... 9 more columns (one for each of the 11 tables)......

JANUARY 2008.......18................18..........
FEBRUARY 2008 ......0.................0..........
MARCH 2008..........0.................0..........
.
. "" with a row for each MONTH/YEAR ""
.
JANUARY 2009........1.................0..........
.
. "" with a row for each MONTH/YEAR ""
.
JANUARY 2010........1.................0..........
.
. "" with a row for each MONTH/YEAR ""
.
DECEMBER 2011.......0.................0..........
+++++++++++++++++++++++++++++++++++++++++++++++++



Sample output:
+++++++++++++++++++++++++++++++++++++++++++++++++

PAGE 1
lastwkco Total
2008 JANUARY 18
2008 FEBRUARY .
2008 MARCH .
2008 APRIL .
2008 MAY .
2008 JUNE .
2008 JULY .
2008 AUGUST .
2008 SEPTEMBER .
2008 OCTOBER .
2008 NOVEMBER .
2008 DECEMBER .
2009 JANUARY 1
2009 FEBRUARY .
2009 MARCH .
2009 APRIL .
2009 MAY .
2009 JUNE .
2009 JULY .
2009 AUGUST .
2009 SEPTEMBER .
2009 OCTOBER .
2009 NOVEMBER .
2009 DECEMBER .
2010 JANUARY 1
2010 FEBRUARY .
2010 MARCH .
2010 APRIL .
2010 MAY .
2010 JUNE .
2010 JULY .
2010 AUGUST .
2010 SEPTEMBER .
2010 OCTOBER .
2010 NOVEMBER .
2010 DECEMBER .
2011 JANUARY 1
2011 FEBRUARY .
2011 MARCH .
2011 APRIL .
2011 MAY .
2011 JUNE .
2011 JULY .
2011 AUGUST .
2011 SEPTEMBER .
2011 OCTOBER .
2011 NOVEMBER .
2011 DECEMBER .
PAGE 2
lastwkco Total
TOTAL 21


PAGE 1
newreferrals Count
2008 JANUARY 18
2008 FEBRUARY .
2008 MARCH .
2008 APRIL .
2008 MAY .
2008 JUNE .
2008 JULY .
2008 AUGUST .
2008 SEPTEMBER .
2008 OCTOBER .
2008 NOVEMBER .
2008 DECEMBER .
2009 JANUARY .
2009 FEBRUARY .
2009 MARCH .
2009 APRIL .
2009 MAY .
2009 JUNE .
2009 JULY .
2009 AUGUST .
2009 SEPTEMBER .
2009 OCTOBER .
2009 NOVEMBER .
2009 DECEMBER .
2010 JANUARY .
2010 FEBRUARY .
2010 MARCH .
2010 APRIL .
2010 MAY .
2010 JUNE .
2010 JULY .
2010 AUGUST .
2010 SEPTEMBER .
2010 OCTOBER .
2010 NOVEMBER .
2010 DECEMBER .
2011 JANUARY .
2011 FEBRUARY .
2011 MARCH .
2011 APRIL .
2011 MAY .
2011 JUNE .
2011 JULY .
2011 AUGUST .
2011 SEPTEMBER .
2011 OCTOBER .
2011 NOVEMBER .
2011 DECEMBER .
PAGE 2
newreferrals Count
TOTAL 18
+++++++++++++++++++++++++++++


I'll see what I can manage with the two helpful hints yall have given me.

Thanks!
Sam

Greetings!

I have two questions, one for each of my helpful folks:

1. Ginny, I believe I want to do my MATCH FILE on MONTH/YEAR, right? If so, how do I set it up in the HOLD file? Right now each value is in &DtDesc (formatted for display) or &CnYrMth (unformatted) and I am not able to figure out how to reference each of the 36 MONTH/YEAR totals in this example from each of the 11 tables for my MATCH.

2. RSQUARED, how do I -SET a COUNT total in the REPEAT loop for each of the 11 tables that I can then reference in a PRINT at the end?

Thanks!
Sam

Sam,

I am assuming that your 11 hold files have virtually the same master. With that said, could you post a couple of them?

And since you already have the hold files and masters, could you give me an idea of what you named them, please?

Hey Ginny, please find below
1. The code for 3 of the 11 tables and
2. The output it currently produces
If I'm not mistaken, this information should answer your questions. Thanks so much for helping me!! Sam

11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111
-* File all.fex
-* File lastwkco.fex
-SET &FROM_YRMTH_INPUT = '12/2007';
-SET &TO_YRMTH_INPUT = '02/2009';
-SET &FROM_YRMTH = EDIT('&FROM_YRMTH_INPUT.EVAL','$$$9999')|EDIT('&FROM_YRMTH_INPUT.EVAL','99');
-SET &TO_YRMTH = EDIT('&TO_YRMTH_INPUT.EVAL','$$$9999')|EDIT('&TO_YRMTH_INPUT.EVAL','99');
-SET &MnthCount = YM(&FROM_YRMTH,&TO_YRMTH,'I3') ;

-* Start File lastwkco.fex
DEFINE FILE TBLBEREAVEMENTBURIAL
DATEYYM/YM = HDATE(DATECLOSED, 'YYMD');
RPTDTRCVD/YM =
IF DATERECEIVED GT DT(18000101000000) THEN
HDATE(DATERECEIVED, 'YYMD')
ELSE
HDATE(DATEADDED, 'YYMD');
END

TABLE FILE TBLBEREAVEMENTBURIAL
COUNT
DATEYYM AS 'Count'
BY DATEYYM
-SET &MnthCount = YM(&FROM_YRMTH,&TO_YRMTH,'I3') ;
-SET &Rows = 'ROWS' ;

-REPEAT DATELOOP FOR &Cntr FROM 0 TO &MnthCount ;
-SET &CnYrMth = AYM(&FROM_YRMTH, &Cntr , 'I6') ;
-SET &DtDesc= CHGDAT('YYM', 'XMYY', &CnYrMth , 'A17');
&Rows
&CnYrMth AS '&DtDesc'
-SET &Rows = 'OVER' ;
-DATELOOP

WHERE DATEYYM FROM &FROM_YRMTH TO &TO_YRMTH
AND (RPTDTRCVD LT DATEYYM)
-*ON TABLE HOLD AS lastwkco
ON TABLE SUBTOTAL
END

-* End File lastwkco.fex
-**********************************************************
-* Start File newreferrals.fex
DEFINE FILE TBLBEREAVEMENTBURIAL
DATEYYM2/YM =
IF DATERECEIVED GT DT(18000101000000) THEN
HDATE(DATERECEIVED, 'YYMD')
ELSE
HDATE(DATEADDED, 'YYMD');
END

TABLE FILE TBLBEREAVEMENTBURIAL
COUNT
DATEYYM2 AS 'Count2'
BY DATEYYM2
-SET &MnthCount = YM(&FROM_YRMTH,&TO_YRMTH,'I3') ;
-SET &Rows = 'ROWS' ;

-REPEAT DATELOOP2 FOR &Cntr FROM 0 TO &MnthCount ;
-SET &CnYrMth = AYM(&FROM_YRMTH, &Cntr , 'I6') ;
-SET &DtDesc= CHGDAT('YYM', 'XMYY', &CnYrMth , 'A17');
&Rows &CnYrMth AS '&DtDesc'
-SET &Rows = 'OVER' ;
-DATELOOP2

WHERE DATEYYM2 FROM &FROM_YRMTH TO &TO_YRMTH
-*ON TABLE HOLD AS newreferrals
ON TABLE SUBTOTAL
END
-* End File newreferrals.fex
-**********************************************************
-* Start File nxtwkco.fex
DEFINE FILE TBLBEREAVEMENTBURIAL
DATEYYM3/YM =
IF DATERECEIVED GT DT(18000101000000) THEN
HDATE(DATERECEIVED, 'YYMD')
ELSE
HDATE(DATEADDED, 'YYMD');
END

TABLE FILE TBLBEREAVEMENTBURIAL
COUNT
DATEYYM3 AS 'Count3'
BY DATEYYM3
-SET &MnthCount = YM(&FROM_YRMTH,&TO_YRMTH,'I3') ;
-SET &Rows = 'ROWS' ;

-REPEAT DATELOOP3 FOR &Cntr FROM 0 TO &MnthCount ;
-SET &CnYrMth = AYM(&FROM_YRMTH, &Cntr , 'I6') ;
-SET &DtDesc= CHGDAT('YYM', 'XMYY', &CnYrMth , 'A17');
&Rows &CnYrMth AS '&DtDesc'
-SET &Rows = 'OVER' ;
-DATELOOP3

WHERE DATEBURIAL IS MISSING
AND DATECLOSED IS MISSING
AND DATEYYM3 FROM &FROM_YRMTH TO &TO_YRMTH
-*ON TABLE HOLD AS nxtwkco
ON TABLE SUBTOTAL
END
-* End File nxtwkco.fex
11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111


22222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222
.............................Count
DECEMBER 2007......
JANUARY 2008.........18
FEBRUARY 2008…......
MARCH 2008.............
APRIL 2008................
MAY 2008.................
JUNE 2008 .................
JULY 2008 .................
AUGUST 2008............
SEPTEMBER 2008.....
OCTOBER 2008.........
NOVEMBER 2008......
DECEMBER 2008......
JANUARY 2009..........1
FEBRUARY 2009........



.............................Count2
DECEMBER 2007.....22
JANUARY 2008.........18
FEBRUARY 2008…......
MARCH 2008.............
APRIL 2008................
MAY 2008.................
JUNE 2008 .................
JULY 2008 .................
AUGUST 2008............
SEPTEMBER 2008.....
OCTOBER 2008.........
NOVEMBER 2008......
DECEMBER 2008......
JANUARY 2009..........
FEBRUARY 2009........


.............................Count3
DECEMBER 2007.......4
JANUARY 2008.........14
FEBRUARY 2008…......
MARCH 2008.............
APRIL 2008................
MAY 2008.................
JUNE 2008 .................
JULY 2008 .................
AUGUST 2008............
SEPTEMBER 2008.....
OCTOBER 2008.........
NOVEMBER 2008......
DECEMBER 2008.......
JANUARY 2009...........
FEBRUARY 2009........

22222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222222

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

Sam,

I am going to make assumptions as to what the hold file names are. I asked for a hold master but you posted the data. So let us see what you can do with this.

MATCH FILE DATECNT1
PRINT COUNT1
BY DATECNT
RUN
FILE DATECNT2
PRINT COUNT2
BY DATECNT
RUN
FILE DATECNT3
PRINT COUNT3
BY DATECNT
RUN
FILE DATECNTn
PRINT COUNTn
BY DATECNT
RUN
FILE DATECNT11
PRINT COUNT11
BY DATECNT
AFTER MATCH HOLD AS MONTH11 OLD-AND-NEW
END
TABLE FILE MONTH11
...
END

If you want, you can use Dialogue Manager to loop through 2 through 10 so you don't have to write the code over so many times.

Hey Ginny, Sorry! I posted the code, too, because I thought the HOLD file names were in the code in the following lines:
ON TABLE HOLD AS lastwkco
...
ON TABLE HOLD AS newreferrals
...
ON TABLE HOLD AS nxtwkco

Following your excellent example, how do I set the value of COUNT1, COUNT2 and COUNT3? Where in this code can I set these variables using COUNT? I've tried many different things and failed each time.
-* Start File lastwkco.fex
DEFINE FILE TBLBEREAVEMENTBURIAL
DATEYYM1/YM = HDATE(DATECLOSED, 'YYMD');
RPTDTRCVD/YM =
IF DATERECEIVED GT DT(18000101000000) THEN
HDATE(DATERECEIVED, 'YYMD')
ELSE
HDATE(DATEADDED, 'YYMD');
END

TABLE FILE TBLBEREAVEMENTBURIAL
COUNT
DATEYYM1 AS 'Count'
BY DATEYYM1
-SET &MnthCount = YM(&FROM_YRMTH,&TO_YRMTH,'I3') ;
-SET &Rows = 'ROWS' ;

-REPEAT DATELOOP FOR &Cntr FROM 0 TO &MnthCount ;
-SET &CnYrMth = AYM(&FROM_YRMTH, &Cntr , 'I6') ;
-SET &DtDesc= CHGDAT('YYM', 'XMYY', &CnYrMth , 'A17');
&Rows
&CnYrMth AS '&DtDesc'
-SET &Rows = 'OVER' ;
-DATELOOP

WHERE DATEYYM1 FROM &FROM_YRMTH TO &TO_YRMTH
AND (RPTDTRCVD LT DATEYYM)
ON TABLE HOLD AS lastwkco
END
-* End File lastwkco.fex

Thanks! Sam

quote:

DATEYYM1 AS 'Count'

Append the &CNTR to your AS name. Or in your match file say PRINT COUNT AS COUNTn.

Also, I would avoid using the word COUNT as a column name as it is a WebFOCUS verb and a reserved word. Call it DATECNT or something like that.

Thanks Ginny! I'll give this a try! Stay tuned...

Hey Ginny, when I tried appending &Cntr...

TABLE FILE TBLBEREAVEMENTBURIAL
COUNT
DATEYYM AS 'TotalCt'|&Cntr
BY DATEYYM
-SET &MnthCount = YM(&FROM_YRMTH,&TO_YRMTH,'I3') ;
-SET &Rows = 'ROWS' ;

-REPEAT DATELOOP FOR &Cntr FROM 0 TO &MnthCount ;
-SET &CnYrMth = AYM(&FROM_YRMTH, &Cntr , 'I6') ;
-SET &DtDesc= CHGDAT('YYM', 'XMYY', &CnYrMth , 'A17');
&Rows
&CnYrMth AS '&DtDesc'
-SET &Rows = 'OVER' ;
-DATELOOP

WHERE DATEYYM FROM &FROM_YRMTH TO &TO_YRMTH
AND (RPTDTRCVD LT DATEYYM)
ON TABLE HOLD AS lastwkco
-*ON TABLE SUBTOTAL
END

...and running the report, it prompted me for Cntr...


Parameters

Cntr
___________

Run Reset Clear Output Run in a new window

1. Specify vlaues for all parameters.
2. Select the run button to submit the request.


Is it because &Cntr is defined in the REPEAT statement after the COUNT statement? If so, how do I fix this? If not, what did I do wrong?

Thanks!
Sam

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

I apoligize for steering you in the wrong direction. According to an earlier post, you have a separate TABLE request for each of the 11 files. then just number them individually as you did in that post. Your goal is to have 11 hold files and masters. The masters for the hold files should be virtually identical except for the name of the date counter field.

I also recommend that you do some reading on Dialogue Manager. I learned it from the manual years ago and the concepts are not difficult.

Hey Ginny, no apology necessary - I appreciate your time and help! So would JOIN be my best bet (at least until I find the time to learn Dialogue Manager (which I've earmarked in the Help Manual, BTW!)

Thanks,
Sam

No. JOIN is not what you should do. MATCH in this case is better.

Let me do an example using your previous posting.

-* Start File lastwkco.fex
DEFINE FILE TBLBEREAVEMENTBURIAL
DATEYYM/YM = HDATE(DATECLOSED, 'YYMD');
RPTDTRCVD/YM =
IF DATERECEIVED GT DT(18000101000000) THEN
HDATE(DATERECEIVED, 'YYMD')
ELSE
HDATE(DATEADDED, 'YYMD');
END

TABLE FILE TBLBEREAVEMENTBURIAL
COUNT
DATEYYM AS DATECNT1
BY DATEYYM
-SET &MnthCount = YM(&FROM_YRMTH,&TO_YRMTH,'I3') ;
-SET &Rows = 'ROWS' ;

-REPEAT DATELOOP FOR &Cntr FROM 0 TO &MnthCount ;
-SET &CnYrMth = AYM(&FROM_YRMTH, &Cntr , 'I6') ;
-SET &DtDesc= CHGDAT('YYM', 'XMYY', &CnYrMth , 'A17');
&Rows
&CnYrMth AS '&DtDesc'
-SET &Rows = 'OVER' ;
-DATELOOP

WHERE DATEYYM FROM &FROM_YRMTH TO &TO_YRMTH
AND (RPTDTRCVD LT DATEYYM)
ON TABLE HOLD AS LASTWKCO FORMAT ALPHA
END

-* End File lastwkco.fex
-**********************************************************
-* Start File newreferrals.fex
DEFINE FILE TBLBEREAVEMENTBURIAL
DATEYYM/YM =
IF DATERECEIVED GT DT(18000101000000) THEN
HDATE(DATERECEIVED, 'YYMD')
ELSE
HDATE(DATEADDED, 'YYMD');
END

TABLE FILE TBLBEREAVEMENTBURIAL
COUNT
DATEYYM AS DATECNT2
BY DATEYYM
-SET &MnthCount = YM(&FROM_YRMTH,&TO_YRMTH,'I3') ;
-SET &Rows = 'ROWS' ;

-REPEAT DATELOOP2 FOR &Cntr FROM 0 TO &MnthCount ;
-SET &CnYrMth = AYM(&FROM_YRMTH, &Cntr , 'I6') ;
-SET &DtDesc= CHGDAT('YYM', 'XMYY', &CnYrMth , 'A17');
&Rows &CnYrMth AS '&DtDesc'
-SET &Rows = 'OVER' ;
-DATELOOP2

WHERE DATEYYM FROM &FROM_YRMTH TO &TO_YRMTH
ON TABLE HOLD AS NEWREFERRALS FORMAT ALPHA
END
-* End File newreferrals.fex
-**********************************************************
-* Start File nxtwkco.fex
DEFINE FILE TBLBEREAVEMENTBURIAL
DATEYYM/YM =
IF DATERECEIVED GT DT(18000101000000) THEN
HDATE(DATERECEIVED, 'YYMD')
ELSE
HDATE(DATEADDED, 'YYMD');
END

TABLE FILE TBLBEREAVEMENTBURIAL
COUNT
DATEYYM AS DATECNT3
BY DATEYYM
-SET &MnthCount = YM(&FROM_YRMTH,&TO_YRMTH,'I3') ;
-SET &Rows = 'ROWS' ;

-REPEAT DATELOOP3 FOR &Cntr FROM 0 TO &MnthCount ;
-SET &CnYrMth = AYM(&FROM_YRMTH, &Cntr , 'I6') ;
-SET &DtDesc= CHGDAT('YYM', 'XMYY', &CnYrMth , 'A17');
&Rows &CnYrMth AS '&DtDesc'
-SET &Rows = 'OVER' ;
-DATELOOP3

WHERE DATEBURIAL IS MISSING
AND DATECLOSED IS MISSING
AND DATEYYM FROM &FROM_YRMTH TO &TO_YRMTH
ON TABLE HOLD AS NXTWKCO FORMAT ALPHA
END
-* End File nxtwkco.fex

-* Begin the MATCH Process
MATCH FILE LASTWKCO 
PRINT DATECNT1
BY DATEYYM
RUN
FILE NEWREFERRALS 
PRINT DATECNT2
BY DATEYYM
RUN
FILE NXTWKCO 
PRINT DATECNT3
BY DATEYYM
AFTER MATCH HOLD AS DATECOUNTS OLD-AND-NEW
END
TABLE FILE DATECOUNTS
PRINT DATECNT1 DATECNT2 DATECNT3
BY DATEYYM
END
  

I changed the BY field in each TABLE to sort the same field so that the name is the same in every hold file. Very simple.

BTW, I'm not quite sure what you are doing in your repeat loop; but if you are going to hold the data to combine, it you don't need to do a ROWS OVER. Please re-examine your need for that code.

Hey Ginny, I used the code you provided (thank you very much!) and I got an error:

0 ERROR AT OR NEAR LINE 99 IN PROCEDURE ginny FOCEXEC *
(FOC003) THE FIELDNAME IS NOT RECOGNIZED: DATECNT1
BYPASSING TO END OF COMMAND
(FOC009) INCOMPLETE REQUEST STATEMENT
(FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: DATECOUNTS
BYPASSING TO END OF COMMAND

LINE 99 is: PRINT DATECNT1 just under the MATCH FILE LASTWKCO statement. Also, for some reason, it didn't like DATECOUNTS.

What did I do wrong this time? Sorry!!!

Thanks,
Sam

Hey Ginny, on your 'BTW' above, I tried running it without the REPEAT LOOPs and it seems that what they are giving me is a row for each MONTH/YEAR whether the COUNT is zero or not, which is what I need for my report. At least, that's my novice guess!

Thanks,
Sam

For your previous post, add

SET ASNAMES=ON

at the beginning of your focexec. Leave your loops in and see what happens.

Regarding your most recent post, you could create a file with all the dates you need and no counts. Then use that as the first file in your match logic. Then you wouldn't need the repeat logic in the individual TABLEs that create the hold files. Once matched, the counters would be zero.

But try the ASNAMES thing first. Sorry I forgot that. Since I can't run your code, it makes it difficult to verify that it is correct.

Samf,

Sorry it took so long to reply, but I do have some work to do. Try after the first report
-SET &R1YYMM1= the count
-SET &R1YYMM2= the count
....
after the second report
-SET &R2YYMM1 = the count
etc
after the final report and -SET
Do a simple TABLE FILE
Read 1 record and
PRINT &R1YYMM1 &R2YYMM2 ...
OVER
&R1YYMM2 &R2YYMM2 ....
You may have to do this as a Header /Footer

Ginny, you have absolutely nothing to apologize for! I greatly appreciate your help!!

I included -SET ASNAMES=ON; at the very beginning of my program just below the fex name comment line. The results are the same:

0 NUMBER OF RECORDS IN TABLE= 19 LINES= 2
0 NUMBER OF RECORDS IN TABLE= 40 LINES= 2
0 NUMBER OF RECORDS IN TABLE= 18 LINES= 2
0 ERROR AT OR NEAR LINE 100 IN PROCEDURE ginny FOCEXEC *
(FOC003) THE FIELDNAME IS NOT RECOGNIZED: DATECNT1
BYPASSING TO END OF COMMAND
(FOC009) INCOMPLETE REQUEST STATEMENT
(FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: DATECOUNTS
BYPASSING TO END OF COMMAND

Take the - off of the SET ASNAMES=ON. You will notice in my post that there is no dash.

OOPS! Ok, I took off the - and reran and got the same results.

ON TABLE HOLD AS LASTWKCO FORMAT ALPHA
END
-RUN
? HOLD LASTWKCO
-EXIT

Put those lines after the first hold and post what you get for the description of the hold file.

I had asked you yesterday to post one of your masters. In lieu of that, this will have to suffice. I'm getting frustrated, as I'm sure that you are, as this should be very easy to do.

Sorry Ginny, I wasn't sure what you meant by 'master'. Do you mean the SQL Server table against which I am running? If so, I will post it right away.

Also, sorry it's frustrating for you. It is not for me at all as I learn something new every time you post and I was quite lost without you, so... sorry for your frustration!

I put in the code (yet another cool thing I've learned from you!) and here is the result:


No HTML Output!
--------------------------------------------------------------------------------
VALID VALUES ARE ASNAMES = ON OFF OR FOCUS OR MIXED
0 NUMBER OF RECORDS IN TABLE= 19 LINES= 2
0DEFINITION OF HOLD FILE: LASTWKCO
0FIELDNAME ALIAS FORMAT
E01 A 17
DATEYYM E02 I5


Thanks again for hanging in there with me!
Sam

RSquared, my apologies for not responding sooner to your latest post. I completely understand about that 'work to do' part!! I shall try a fex with your suggestions in it & let yall know what I find out. The more I try, the more I learn!

Thanks!
Sam

quote:

VALID VALUES ARE ASNAMES = ON OFF OR FOCUS OR MIXED

Sam,


This bothers me. It looks like you don't have the ASNAMES set properly. Also the fact that your date counter name is E01 virtually confirms that fact.

The syntax is SET ASNAMES=ON. Please put it in the program right after your Dialogue Manager sets and just before your first DEFINE and TABLE set of code, run it again and post the results.

I have taken some dates out of the CENTURYSALES demo file and created this little code snippet which resembles (minus the looping and where clauses) what each of your 11 hold file creation table requests should look like.

APP PREPENDPATH IBIDEMO
SET ASNAMES=ON
DEFINE FILE CENTURYSALES
DATEYYM/YYM=YYEARMONTH;
END
TABLE FILE CENTURYSALES
COUNT
ORDERDATE AS DATECNT1
BY DATEYYM
ON TABLE HOLD AS LASTWKCO FORMAT ALPHA
END
-RUN
? HOLD LASTWKCO  

Here are the results:

 0 NUMBER OF RECORDS IN TABLE=    32283  LINES=     24
 0DEFINITION OF HOLD FILE: LASTWKCO
 0FIELDNAME                         ALIAS         FORMAT
 DATEYYM                           E01           YYM
 DATECNT1                          E02           I9
  

Make sure you have the BY DATEYYM in your program so that your hold files are sorted properly. For your first file, your output (except for the line counts) should look exactly like what I posted here.

quote:

I wasn't sure what you meant by 'master'. Do you mean the SQL Server table against which I am running?

As for this, when you say ON TABLE HOLD ..., you create a file and a master file description. Since in this case, they are both being written to temporary disk (EDATEMP), you would have to put an APP HOLD dirname in your focexec to trap them (which might not be a bad idea until we get this worked out). For now, however, the ? HOLD holdname will work just fine.

So, in summary, make sure that your code, with the caveats specified, looks like mine and the hold output looks like mine. Until it does, you cannot proceed with the MATCH logic.

In the meantime, I think I'll play around with CENTURYSALES and try to emulate your program and use REGION or something like that to separate the date counters.

Well, SamIam, I think I have something. Now you are going to have to match up this code with yours and delete all those loopy things. But this should work.

 -SET &ECHO=ALL;
-SET &FROM_YRMTH = '200511';
-SET &TO_YRMTH = '200606';
-*-SET &FROM_YRMTH = EDIT('&FROM_YRMTH_INPUT.EVAL','$$$9999')|EDIT('&FROM_YRMTH_INPUT.EVAL','99');
-*-SET &TO_YRMTH = EDIT('&TO_YRMTH_INPUT.EVAL','$$$9999')|EDIT('&TO_YRMTH_INPUT.EVAL','99');
-SET &MnthCount = YM(&FROM_YRMTH,&TO_YRMTH,'I3') ;
-TYPE FROM_YRMTH &FROM_YRMTH
-TYPE TO_YRMTH   &TO_YRMTH 
-TYPE MnthCount  &MnthCount 

-* Create a temporary master for all the required months.
FILEDEF MOLISTMAS DISK monthlist.mas
-RUN
-WRITE MOLISTMAS FILENAME=MONTHLIST,SUFFIX=FIX
-WRITE MOLISTMAS SEGNAME=DATESEG,SEGTYPE=S0
-WRITE MOLISTMAS FIELDNAME=DATEYYM,,FORMAT=YYM,ACTUAL=A6,$

-* Load the temp file with all the months.  To be used in the match later.
FILEDEF MONTHLIST DISK monthlist.ftm
-RUN
-REPEAT DATELOOP FOR &Cntr FROM 0 TO &MnthCount ;
-SET &CnYrMth = AYM(&FROM_YRMTH, &Cntr , 'I6') ;
-WRITE MONTHLIST &CnYrMth 
-DATELOOP
-*TABLE FILE MONTHLIST
-*PRINT *
-*END
-*-EXIT
APP PREPENDPATH IBIDEMO
SET ASNAMES=ON
DEFINE FILE CENTURYSALES
DATEYYM/YYM=YYEARMONTH;
END
TABLE FILE CENTURYSALES
COUNT
ORDERDATE AS DATECNT1
BY DATEYYM
WHERE DATEYYM FROM &FROM_YRMTH TO &TO_YRMTH
-* Next line to omit a month to show how all the lines get included at the end.
WHERE DATEYYM NE 200603
WHERE PLANTREGION EQ 'New England'
ON TABLE HOLD AS LASTWKCO FORMAT ALPHA
END
-*-EXIT
-RUN
? HOLD LASTWKCO
TABLE FILE CENTURYSALES
COUNT
ORDERDATE AS DATECNT2
BY DATEYYM
WHERE DATEYYM FROM &FROM_YRMTH TO &TO_YRMTH
-* Next line to omit a month to show how all the lines get included at the end.
WHERE DATEYYM NE 200512
WHERE PLANTREGION EQ 'Pacific'
ON TABLE HOLD AS NEWREFERRALS FORMAT ALPHA
END
TABLE FILE CENTURYSALES
COUNT
ORDERDATE AS DATECNT3
BY DATEYYM
WHERE DATEYYM FROM &FROM_YRMTH TO &TO_YRMTH
-* Next line to omit a month to show how all the lines get included at the end.
WHERE DATEYYM NE 200601
WHERE PLANTREGION EQ 'South Atlantic'
ON TABLE HOLD AS NXTWKCO FORMAT ALPHA
END

MATCH FILE MONTHLIST
BY DATEYYM
RUN
FILE LASTWKCO 
PRINT DATECNT1
BY DATEYYM
RUN
FILE NEWREFERRALS 
PRINT DATECNT2
BY DATEYYM
RUN
FILE NXTWKCO 
PRINT DATECNT3
BY DATEYYM
AFTER MATCH HOLD AS DATECOUNTS OLD-OR-NEW
END
TABLE FILE DATECOUNTS
PRINT DATECNT1 DATECNT2 DATECNT3
BY DATEYYM
END

One more observation and I'm not sure that this is in your current version of the program.

quote:

AND (RPTDTRCVD LT DATEYYM)

Comparing a data base field against a defined field when going against a relational data base as you are is a big NO-NO. That WHERE clause will never be passed to the data base. All the data will be returned to WebFOCUS and WF will do the WHERE. And in your case, this phrase is not necessary.

If the example above doesn't work for you, I'm turning in my WF card.

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