Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     More first and Last info
Go
New
Search
Notify
Tools
Reply
  
More first and Last info
 Login/Join
 
Silver Member
posted
I have the following fields
DATE LASTODOMREADING Thisodomreading
6/28/2002 535 666
6/30/2002 666 767


If I do a report where I just want the LastOdomreading from the beginning of the date range and the THISodomreading from the ending of the date range how can I do this?

So the data to be returned should be
Date Range Lowest Odom HighestODOM
6/28 - 6/30 535 767
 
Posts: 45 | Registered: March 18, 2005Reply With QuoteReport This Post
Platinum Member
posted Hide Post
this sample is against the employee.foc db
see if this is what you want.

TABLE FILE EMPLOYEE
SUM
COMPUTE START_SAL/D12.2 = LST.SALARY;
COMPUTE END_SAL/D12.2 = FST.SALARY;
BY EMP_ID
END


WF 8 version 8.2.04. Windows.
In focus since 1990.
 
Posts: 189 | Location: pgh pa | Registered: October 06, 2004Reply With QuoteReport This Post
Guru
posted Hide Post
Could you just use the prefix of MIN. and MAX. to get what you want?
 
Posts: 406 | Location: Canada | Registered: May 31, 2004Reply With QuoteReport This Post
Guru
posted Hide Post
min and max refer to values while fst and lst refer to physical instances.
 
Posts: 252 | Location: USA | Registered: April 15, 2003Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Ginny,

This should do it:

TABLE FILE ODOM
PRINT
LASTREADING
THISREADING
COMPUTE
COUNTER/I5 = COUNTER + 1;
BY RDATE
ON TABLE HOLD AS ODOMETER
END
-RUN

-SET &NUMRECS = &LINES;

DEFINE FILE ODOMETER
FIRSTDATE/MDYY = IF COUNTER EQ 1 THEN RDATE ELSE FIRSTDATE;
LASTDATE/MDYY = IF COUNTER EQ &NUMRECS THEN RDATE ELSE FIRSTDATE;
LASTREAD/I7 = IF COUNTER EQ 1 THEN LASTREADING ELSE 0;
THISREAD/I7 = IF COUNTER EQ &NUMRECS THEN THISREADING ELSE 0;
END

TABLE FILE ODOMETER
PRINT
FIRSTDATE
LASTDATE
LASTREAD
THISREAD
ON TABLE HOLD AS FINAL
END

TABLE FILE FINAL
SUM
FIRSTDATE
LASTDATE
LASTREAD
THISREAD
END

In case it isn't clear: In that first step, ODOM is your source file and RDATE is your date field. It's a lot of steps, I know, but I can't think of a way to do it in fewer. If any of it is confusing, I would suggest taking the HOLD out of the first step to see the results, then putting it back and taking HOLD out of the second step.


dwf
 
Posts: 135 | Location: Portland, OR | Registered: March 23, 2005Reply With QuoteReport This Post
Silver Member
posted Hide Post
Thanks, I appreciate the help. I have decided it's just easier to do all of the work through views on the SQL server and then just use Webfocus to pull the results.
 
Posts: 45 | Registered: March 18, 2005Reply With QuoteReport This Post
Master
posted Hide Post
You shoud be able to do a simple table request

  
TABLE FILE xxx
PRINT
MIN.LASTODOMREADING
MAX.Thisodomreading
AND COMPUTE ROWCNT/I9 = IF DATE EQ LAST DATE THEN LAST ROWCNT + 1 ELSE 1; NOPRINT
BY DATE
WHERE TOTAL ROWCNT EQ 1;
END




Scott

 
Posts: 865 | Registered: May 24, 2004Reply With QuoteReport This Post
Virtuoso
posted Hide Post
I think we went off the deep end with complicating this request. The following should work.

TABLE FILE xxx
SUM FST.LASTODOMREADING LST.Thisodomreading
END

When using an RDBMS as the data source, the rules change a bit for using FST. and LST. so verify that this gives you what you are looking for.

Maybe one of th FOCWizards could expound on the usage of FST. and LST. when reporting from an RDBMS. Is this documented anywhere?


Thanks!

Mickey

FOCUS/WebFOCUS 1990 - 2011
 
Posts: 995 | Location: Gaithersburg, MD, USA | Registered: May 07, 2003Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Ginny, There is some information about the usage of FST. and LST. in the Interface Manual specific to your RDBMS. Basically it looks like it should work as advertised as long as the KEYS setting in the FOCUS Access File Descriptions for your SQL Server TABLES are set properly.


Thanks!

Mickey

FOCUS/WebFOCUS 1990 - 2011
 
Posts: 995 | Location: Gaithersburg, MD, USA | Registered: May 07, 2003Reply With QuoteReport This Post
<DocServices>
posted
An explanation of FST. and LST. with regard to relational databases, is in the FOCUS for S/390 Relational Data Adapter User's Manual Version 7.2 Try page 4-28 (involving KEYORDER), and page 7-12, where it discusses translating FST. and LST. to SQL MIN and MAX.

Hope this helps.

Regards,
Jenn
 
Reply With QuoteReport This Post
Platinum Member
posted Hide Post
One frivolous note: M's solution will only work if the lowest odometer reading is associated with the lowest date, and the highest reading is associated with the highest date. However, since your data appears to be so organized, it ought to work, barring any mistakes in data entry.


dwf
 
Posts: 135 | Location: Portland, OR | Registered: March 23, 2005Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     More first and Last info

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.