HOW TO REFERENCE COUNTS FROM MULTIPLE TABLES

Expert

posted

quote:
BTW, on a couple of the 11 reports, I am actually comparing against 2 different dates in the SQL Server table. One of them was the 'AND (RPTDTRCVD LT DATEYYM) '. How should I handle it?

If the format of RPTDTRCVD is YYMD or a datetime stamp, then you should compare it to another date base field with the same format.

Maybe you could give an example. But wait until you get the rest of this code figured out before we tackle this one. I need a rest.

Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google

Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006

Ignored post by GinnyJakes posted

Gold member

posted

Well, I've been up to my elbows in alligators all afternoon and have not gotten a chance to work on my reports using your wonderful code. I may have to wait until Monday but please do stay tuned!

Thank you and sorry for the delay!!
SamIam (I like my new name! Thanks!)

WebFOCUS 7.6.4
Developer Studio on Windows XP Professional, Version 2002, Service Pack 2
Output: PDF

Posts: 70 | Registered: January 25, 2008

Ignored post by SamF posted

Expert

posted

From Dr. Seuss, Green Eggs and Ham, I do not like them, Sam I Am.

Have a nice weekend.

Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google

Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006

Ignored post by GinnyJakes posted

Gold member

posted

Hey Ginny, I'm finding some time early this morning to work on my code. I'm building the code one table at a time. Here are the results from my first table.

FROM_YRMTH 200712
TO_YRMTH 200902
MnthCount 14
0 NUMBER OF RECORDS IN TABLE= 19 LINES= 2
0DEFINITION OF HOLD FILE: LASTWKCO
0FIELDNAME....ALIAS..........FORMAT
DATEYYM..........E01...........YM
DATECNT1........E02...........I5

I think they look good. What do you think?

Now before I continue, I have a quick question...
Does it matter that the 'COUNT BY' field in my next table needs to be a different date in my SQL Server Table than the first table? I noticed that all of yours used the same COUNT BY date of ORDERDATE.

Thanks!
SamIam

WebFOCUS 7.6.4
Developer Studio on Windows XP Professional, Version 2002, Service Pack 2
Output: PDF

Posts: 70 | Registered: January 25, 2008

Ignored post by SamF posted

Expert

posted

Yes, your hold file description looks good.

And, no, it doesn't matter that a different table is counting a different date. What matters is that the hold file definitions all look virtually the same, except for the name of the count field.

Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google

Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006

Ignored post by GinnyJakes posted

Gold member

posted

Thanks, Ginny! Ok, I have it running but the results are unexpected. Before adding the 'MATCH' logic, here is what displays:

FROM_YRMTH 200712
TO_YRMTH 200902
MnthCount 14
MnthList 200712
MnthList 200801
MnthList 200802
MnthList 200803
MnthList 200804
MnthList 200805
MnthList 200806
MnthList 200807
MnthList 200808
MnthList 200809
MnthList 200810
MnthList 200811
MnthList 200812
MnthList 200901
MnthList 200902
0 NUMBER OF RECORDS IN TABLE= 19 LINES= 2
0DEFINITION OF HOLD FILE: LASTWKCO
0FIELDNAME ALIAS FORMAT
RPTDTCLSD E01 YM
DATECNT1 E02 I5
0 NUMBER OF RECORDS IN TABLE= 40 LINES= 2
0DEFINITION OF HOLD FILE: NEWREFER
0FIELDNAME ALIAS FORMAT
RPTDTRCVD E01 YM
DATECNT2 E02 I5
0 NUMBER OF RECORDS IN TABLE= 18 LINES= 2
0DEFINITION OF HOLD FILE: NXTWKCO
0FIELDNAME ALIAS FORMAT
RPTDTRCVD E01 YM
DATECNT3 E02 I5

Then after I add the match logic, here's what displays:

RPTDTCLSD … DATECNT1 …...... DATECNT2 …...... DATECNT3
1908/01……………….18……………………0……………………… 4
1909/01……………….1…………………….. 0……………………. 14
2007/12………………. 0 ……………………0……………………… 0
2008/01………………. 0…………………… 0 ……………………… 0
2008/02………………. 0…………………… 0……………………… 0
2008/03………………. 0…………………… 0……………………… 0
2008/04………………. 0 ……………………0 ……………………… 0
2008/05………………. 0 ……………………0 ……………………… 0
2008/06………………. 0 ……………………0……………………… 0
2008/07………………. 0 ……………………0……………………… 0
2008/08………………. 0 ……………………0 ……………………… 0
2008/09………………. 0 ……………………0 ……………………… 0
2008/10 ……………….0…………………… 0……………………… 0
2008/11 ……………….0 ……………………0 ……………………… 0
2008/12………………. 0 ……………………0……………………… 0
2009/01 ……………….0 ……………………0……………………… 0
2009/02………………. 0…………………… 0……………………… 0

I wasn’t expecting 1908/01 or 1909/01 and the totals are incorrect. What did I mess up? Here’s the code…

-* File Ginny2.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') ;
-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=RPTDTCLSD,,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 LOADLOOP FOR &Cntr FROM 0 TO &MnthCount ;
-SET &LdCnYrMth = AYM(&FROM_YRMTH, &Cntr , 'I6') ;
-WRITE MONTHLIST &LdCnYrMth
-TYPE MnthList &LdCnYrMth
-LOADLOOP
-*TABLE FILE MONTHLIST
-*PRINT *
-*END
-*-EXIT

APP PREPENDPATH IBIDEMO
SET ASNAMES=ON
DEFINE FILE TBLBEREAVEMENTBURIAL
RPTDTCLSD/YM = HDATE(DATECLOSED, 'YYMD');
RPTDTRCVD/YM =
IF DATERECEIVED GT DT(18000101000000) THEN
HDATE(DATERECEIVED, 'YYMD')
ELSE
HDATE(DATEADDED, 'YYMD');
END

TABLE FILE TBLBEREAVEMENTBURIAL
COUNT
DATECLOSED AS DATECNT1
BY RPTDTCLSD
WHERE RPTDTCLSD FROM &FROM_YRMTH TO &TO_YRMTH
WHERE RPTDTRCVD LT RPTDTCLSD
ON TABLE HOLD AS LASTWKCO FORMAT ALPHA
END
-*-EXIT
-RUN
? HOLD LASTWKCO

TABLE FILE TBLBEREAVEMENTBURIAL
COUNT
RPTDTRCVD AS DATECNT2
BY RPTDTRCVD
WHERE RPTDTRCVD FROM &FROM_YRMTH TO &TO_YRMTH
ON TABLE HOLD AS NEWREFERRALS FORMAT ALPHA
END
-*-EXIT
-RUN
? HOLD NEWREFERRALS

TABLE FILE TBLBEREAVEMENTBURIAL
COUNT
RPTDTRCVD AS DATECNT3
BY RPTDTRCVD
WHERE RPTDTRCVD FROM &FROM_YRMTH TO &TO_YRMTH
WHERE DATECLOSED IS MISSING
ON TABLE HOLD AS NXTWKCO FORMAT ALPHA
END
-*-EXIT
-RUN
? HOLD NXTWKCO

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

TABLE FILE DATECOUNTS
PRINT DATECNT1 DATECNT2 DATECNT3
BY RPTDTCLSD
END

Thanks!
SamIam

WebFOCUS 7.6.4
Developer Studio on Windows XP Professional, Version 2002, Service Pack 2
Output: PDF

Posts: 70 | Registered: January 25, 2008

Ignored post by SamF posted

Expert

posted

First, you don't need the APP PREPENDPATH IBIDEMO. I only had that in there because the demo file I was going against was in that directory. You have to point to the directory where your masters are.

Secondly, and most importantly, and as I have told you many times, THE HOLD MASTERS HAVE TO LOOK EXACTLY THE SAME. Therefore, in each of your tables before the match, please make sure that your by phrase reads:

BY datename AS DATEYYM

Then make your match look like mine. You will notice that ALL my holds have a DATEYYM. If you do not do that, Focus will not know how to match them up.

Sorry I yelled but you are making this harder than it is. Please make sure that you study and run the code that I gave you. It runs from a demo file and should give you a great idea about what is going on under the covers. Once you understand that, you will be able to use this technique many times over.

Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google

Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006

Ignored post by GinnyJakes posted

Gold member

posted

Hey Ginny, no apology necessary for the 'yelling'! I'm over here yelling at myself!

I think this is all too far over my head to continue to try and figure it out in this forum. It seems I need to at least learn the basics before I continue. I will go to my bosses and explain my situation. They've been discussing using SQL Server Reporting in place of WebFOCUS though I was hoping to dissuade them by my stellar WebFOCUS reporting skills... :-)

I'm sorry to have wasted your time and I'm very grateful for all you've taught me! I know SO much more now than I did when I started. Plus, I have a new nickname!

Thanks again!
SamIam

WebFOCUS 7.6.4
Developer Studio on Windows XP Professional, Version 2002, Service Pack 2
Output: PDF

Posts: 70 | Registered: January 25, 2008

Ignored post by SamF posted

Expert

posted

Sam,

Don't quit. You are very close. All you have to do is add AS DATEYYM to your individual reports and then use that name, DATEYYM, in the matches.

Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google

Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006

Ignored post by GinnyJakes posted

Gold member

posted

Hey Ginny, I thought I was getting close and then I got very confused. I am more than willing to keep trying but I don't want to be a burden on this forum. If you think it's ok for me to keep trying, I'd like that very much! I'm going to try to put the code in based on your latest helpful comments. Stay tuned...

Thanks so much for hanging in there with me!!!
SamIam

WebFOCUS 7.6.4
Developer Studio on Windows XP Professional, Version 2002, Service Pack 2
Output: PDF

Posts: 70 | Registered: January 25, 2008

Ignored post by SamF posted

Gold member

posted

Ok, Ginny, I think I have the HOLD MASTER files looking the same. To be sure, I took the MATCH logic out & ran it and all three tables have 2 fields:
DATEYYM (format YM)
DATECNT# (format I5)

Also, my MonthList table displays 200712 (December, 2007) - 200902 (February, 2009).

Am I on the right track so far?

Thanks!
Sam

WebFOCUS 7.6.4
Developer Studio on Windows XP Professional, Version 2002, Service Pack 2
Output: PDF

Posts: 70 | Registered: January 25, 2008

Ignored post by SamF posted

Gold member

posted

Well, Ginny, if I've not said it often enough or recently enough... THANK YOU!!!!!!!!!!!!! I now have my report working like a charm!!!!!!!! You were right about Dialogue Manager... you were right about MATCH... you were right about me being so close and not giving up... you were right about everything!!!! Now I WILL go to my management and show off my stellar WebFOCUS reporting skills!!! FORGET SQL SERVER REPORTING!!!!!!!!

Anyway, thanks again so much! I'm so grateful that you were more patient with me than I was with myself!!! No doubt, I will have other issues to post to this forum in the future and when I do, I hope I'm lucky enough to get your help again!

Sincerely,
SamIam

P.S. Here's the final code in case any other newbies have been following this thread and need to see the finished product...

-* File Ginny2.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') ;
-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 LOADLOOP FOR &Cntr FROM 0 TO &MnthCount ;
-SET &LdCnYrMth = AYM(&FROM_YRMTH, &Cntr , 'I6') ;
-WRITE MONTHLIST &LdCnYrMth
-TYPE MnthList &LdCnYrMth
-LOADLOOP

SET ASNAMES=ON

DEFINE FILE TBLBEREAVEMENTBURIAL
RPTDTCLSD/YYM = HDATE(DATECLOSED, 'YYMD');
RPTDTRCVD/YYM =
IF DATERECEIVED GT DT(18000101000000) THEN
HDATE(DATERECEIVED, 'YYMD')
ELSE
HDATE(DATEADDED, 'YYMD');
END

TABLE FILE TBLBEREAVEMENTBURIAL
COUNT
RPTDTCLSD AS DATECNT1
BY RPTDTCLSD AS DATEYYM
WHERE RPTDTCLSD FROM &FROM_YRMTH TO &TO_YRMTH
WHERE RPTDTRCVD LT RPTDTCLSD
ON TABLE HOLD AS LASTWKCO FORMAT ALPHA
END
-*-EXIT
-RUN
? HOLD LASTWKCO

TABLE FILE TBLBEREAVEMENTBURIAL
COUNT
RPTDTRCVD AS DATECNT2
BY RPTDTRCVD AS DATEYYM
WHERE RPTDTRCVD FROM &FROM_YRMTH TO &TO_YRMTH
ON TABLE HOLD AS NEWREFERRALS FORMAT ALPHA
END
-*-EXIT
-RUN
? HOLD NEWREFERRALS

TABLE FILE TBLBEREAVEMENTBURIAL
COUNT
RPTDTRCVD AS DATECNT3
BY RPTDTRCVD AS DATEYYM
WHERE RPTDTRCVD FROM &FROM_YRMTH TO &TO_YRMTH
WHERE DATECLOSED IS MISSING
ON TABLE HOLD AS NXTWKCO FORMAT ALPHA
END
-*-EXIT
-RUN
? HOLD NXTWKCO

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 AS 'Last Week Carry Overs'
DATECNT2 AS 'New Referrals'
DATECNT3 AS 'Next Week Carry Overs'
BY DATEYYM AS 'Year / Month'
END

WebFOCUS 7.6.4
Developer Studio on Windows XP Professional, Version 2002, Service Pack 2
Output: PDF

Posts: 70 | Registered: January 25, 2008

Ignored post by SamF posted

Expert

posted

Happy, happy, joy, joy!

I knew you could do it!

Now that it is over, do you see how easy it was? And even better, could you use the techniques to solve another problem?

Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google

Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006

Ignored post by GinnyJakes posted

Gold member

posted

Hey Ginny,

I'm not so sure I did it - - more like YOU did it! But NEXT time I can do it!!!

ABSOLUTELY I can use these techniques to solve all kinds of other problems!! This was a most valuable learning experience!!

Thanks again!!!

WebFOCUS 7.6.4
Developer Studio on Windows XP Professional, Version 2002, Service Pack 2
Output: PDF

Posts: 70 | Registered: January 25, 2008

Ignored post by SamF posted