Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


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

Read-Only Read-Only Topic
Go
Search
Notify
Tools
More first and Last info
 Login/Join
 
Ginny
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, 2005Report This Post
Spence
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, 2004Report This Post
reFOCUSing
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, 2004Report This Post
jimster06
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, 2003Report This Post
dwf
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, 2005Report This Post
Ginny
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, 2005Report This Post
TexasStingray
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, 2004Report This Post
mgrackin
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, 2003Report This Post
mgrackin
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, 2003Report 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
 
Report This Post
dwf
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, 2005Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

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

Copyright © 1996-2020 Information Builders