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     aggregation and record selection

Read-Only Read-Only Topic
Go
Search
Notify
Tools
aggregation and record selection
 Login/Join
 
Platinum Member
posted
If you ran this code,

TABLE FILE EMPLOYEE
SUM CURR_SAL
BY DEPARTMENT
BY HIRE_DATE
END


You get:
DEPARTMENT HIRE_DATE CURR_SAL 
           06/04/12 $11,000.00 
MIS        81/07/01 $31,680.00 
           81/11/02 $27,062.00 
           82/04/01 $30,780.00   
           82/05/01 $18,480.00 
 PRODUCTION 82/01/04 $36,362.00 
             82/02/02 $16,100.00 
             82/07/01 $21,120.00 
             82/08/01 $29,700.00 

I'd like to modify the query to get the rows with the highest hire_date that has a total curr_sal that is GT $30,0000 by department.
You should then only return rows 4 and 6.

Is it possible to do this in one pass at the data using a combination of WHERE TOTAL and BY TOTAL?

Thanks,
Bethany

WF 7.1.3
DevStudio 7.1.4

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


Server Environment: Win2K3 Server WebFOCUS 7.13 Apache Tomcat standalone application server
 
Posts: 188 | Registered: April 14, 2005Report This Post
Platinum Member
posted Hide Post
I think this would work.

TABLE FILE EMPLOYEE
SUM CURR_SAL
BY DEPARTMENT
BY highest 1 HIRE_DATE
if total curr_sal gt 30000
END

Good luck

ET


FOCUS 7.6 MVS PDF,HTML,EXCEL
 
Posts: 115 | Location: Chicago, IL | Registered: May 28, 2004Report This Post
Platinum Member
posted Hide Post
Thanks for the suggestion ET, but that code returns no records.


Server Environment: Win2K3 Server WebFOCUS 7.13 Apache Tomcat standalone application server
 
Posts: 188 | Registered: April 14, 2005Report This Post
Virtuoso
posted Hide Post
You wouldn't even need the "IF TOTAL". Just an "IF" would work.


Regards,

Darin



In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Report This Post
Virtuoso
posted Hide Post
quote:
TABLE FILE EMPLOYEE
SUM CURR_SAL
BY DEPARTMENT
BY highest 1 HIRE_DATE
if total curr_sal gt 30000
END


If you take out the if total, you will see there are no records greater than 30000. Try 20000.


Leah
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Report This Post
Platinum Member
posted Hide Post
What if there were 2 rows that came out for the highest date and one of the rows came out less than 30000 while the other row was greater ? Conversely what if the highest 1 date row had an amount less than 30000? I still think the if total is needed though maybe not in this example.

good luck

ET


FOCUS 7.6 MVS PDF,HTML,EXCEL
 
Posts: 115 | Location: Chicago, IL | Registered: May 28, 2004Report This Post
Platinum Member
posted Hide Post
When you sum by department and hire_date there are 3 records with totals higher than 30,000-2 for the MIS dept and 1 for PRODUCTION.

Using just IF CURR_SAL GT 30000, also returns no records.


Server Environment: Win2K3 Server WebFOCUS 7.13 Apache Tomcat standalone application server
 
Posts: 188 | Registered: April 14, 2005Report This Post
Expert
posted Hide Post
DEPARTMENT HIRE_DATE CURR_SAL 
           06/04/12  $11,000.00 
MIS        81/07/01  $31,680.00 
           81/11/02  $27,062.00 
           82/04/01  $30,780.00 
           82/05/01  $18,480.00 
PRODUCTION 82/01/04  $36,362.00 
           82/02/02  $16,100.00 
           82/07/01  $21,120.00 
           82/08/01  $29,700.00


Interesting, BY HIGHEST 1 will return no values because it appears that WHERE TOTAL is filtering on each of the rows instead of the total by department. It looks to me that you need to do this in two passes.

TABLE FILE EMPLOYEE
SUM 
CURR_SAL
BY DEPARTMENT
BY HIGHEST HIRE_DATE
WHERE TOTAL CURR_SAL GT 30000
ON TABLE HOLD
END

TABLE FILE HOLD
SUM 
CURR_SAL
BY DEPARTMENT
BY HIGHEST 1 HIRE_DATE
END


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Virtuoso
posted Hide Post
Bethany,

Try:
TABLE FILE EMPLOYEE
SUM CURR_SAL
BY DEPARTMENT
BY HIGHEST 1 HIRE_DATE
WHERE TOTAL TOT.CURR_SAL GT 30000;
END


Ignore, this must be a load of cods!

This message has been edited. Last edited by: Alan B,


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Platinum Member
posted Hide Post
The test was and if total test (if TOTAL curr_sal gt tnat 30000) and not just an if test.


FOCUS 7.6 MVS PDF,HTML,EXCEL
 
Posts: 115 | Location: Chicago, IL | Registered: May 28, 2004Report This Post
Virtuoso
posted Hide Post
Okay, after last flop, try:
TABLE FILE EMPLOYEE
SUM CURR_SAL
COMPUTE
XSAL/I1=IF CURR_SAL GT 30000 THEN 1 ELSE 2;
COMPUTE
XDAT/I6YMD=IF DEPARTMENT NE LAST DEPARTMENT AND XSAL EQ 1 THEN HIRE_DATE ELSE
           IF DEPARTMENT NE LAST DEPARTMENT AND XSAL EQ 2 THEN 0 ELSE
           IF XSAL EQ 1 AND DEPARTMENT EQ LAST DEPARTMENT AND LAST XDAT GT HIRE_DATE THEN LAST XDAT ELSE
           IF XSAL EQ 1 AND DEPARTMENT EQ LAST DEPARTMENT AND LAST XDAT LT HIRE_DATE THEN HIRE_DATE ELSE
           LAST XDAT;
BY DEPARTMENT
BY HIRE_DATE
WHERE TOTAL XSAL EQ 1;
WHERE TOTAL HIRE_DATE EQ XDAT;
END

And 'cos it's late you should be able to improve my COMPUTE!


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Platinum Member
posted Hide Post
Looks to me like Alan is almost there. Ran his code and got:

PAGE 1

DEPARTMENT HIRE_DATE CURR_SAL XSAL XDAT
MIS 81/07/01 $31,680.00 1 81/07/01
82/04/01 $30,780.00 1 82/04/01
PRODUCTION 82/01/04 $36,362.00 1 82/01/04

Too many rows. But change BY HIRE_DATE to BY HIGHEST HIRE_DATE and you get the results you're looking for. Which I know Alan would have figured out in about two minnutes.


dwf
 
Posts: 135 | Location: Portland, OR | Registered: March 23, 2005Report This Post
Virtuoso
posted Hide Post
DWF

Thank you, you are correct. I missed the HIGHEST transposing from one machine to another!
Should be:
TABLE FILE EMPLOYEE
SUM CURR_SAL
COMPUTE
XSAL/I1=IF CURR_SAL GT 30000 THEN 1 ELSE 2;
COMPUTE
XDAT/I6YMD=IF DEPARTMENT NE LAST DEPARTMENT AND XSAL EQ 1 THEN HIRE_DATE ELSE
           IF DEPARTMENT NE LAST DEPARTMENT AND XSAL EQ 2 THEN 0 ELSE
           IF XSAL EQ 1 AND DEPARTMENT EQ LAST DEPARTMENT AND LAST XDAT GT HIRE_DATE THEN LAST XDAT ELSE
           IF XSAL EQ 1 AND DEPARTMENT EQ LAST DEPARTMENT AND LAST XDAT LT HIRE_DATE THEN HIRE_DATE ELSE
           LAST XDAT;
BY DEPARTMENT
BY HIGHEST HIRE_DATE
WHERE TOTAL XSAL EQ 1;
WHERE TOTAL HIRE_DATE EQ XDAT;
END


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Gold member
posted Hide Post
Hi everybody,

Try this:

TABLE FILE EMPLOYEE
SUM CURR_SAL
BY DEPARTMENT
BY TOTAL HIGHEST 1 HIRE_DATE
BY HIGHEST HIRE_DATE
WHERE TOTAL CURR_SAL GT 30000
END

With this I got the expected result.


Roland

Prod: WF 7.1.5
Test: WF 7.6.4
Unix Sun Solaris
HTML, PDF, EXL2K
 
Posts: 54 | Location: Switzerland | Registered: May 13, 2003Report This Post
Virtuoso
posted Hide Post
Roland, you are right. Perfect, well done.

Don't know why I could not get that to work yesterday, I was obviously wrong.

(Ah,I see why, I got hung up on TOTAL TOT.CURR_SAL!)


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Platinum Member
posted Hide Post
Thanks to everyone for you help!

Bethany


Server Environment: Win2K3 Server WebFOCUS 7.13 Apache Tomcat standalone application server
 
Posts: 188 | Registered: April 14, 2005Report This Post
Expert
posted Hide Post
quote:
BY TOTAL HIGHEST 1 HIRE_DATE


Roland,

That's something I've not done before! Thanks for letting this out of the bag!

Cheers,

Francis.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Gold member
posted Hide Post
Everybody welcome.

It's the process WF goes through in preparing a report.
Once all the data are in the internal matrix, WF is doing (and in this order):

  • COMPUTEs
  • WHERE/IF TOTAL
  • BY TOTAL

So if you are looking at the code and going through these steps, you can see.


Roland

Prod: WF 7.1.5
Test: WF 7.6.4
Unix Sun Solaris
HTML, PDF, EXL2K
 
Posts: 54 | Location: Switzerland | Registered: May 13, 2003Report 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     aggregation and record selection

Copyright © 1996-2020 Information Builders