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     [SOLVED]ACROSS HIGHEST TOTAL

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED]ACROSS HIGHEST TOTAL
 Login/Join
 
Anatess
Guru
posted
I thought this would be simple, but I can't seem to make something work except to incur overhead with a double pass and a join.

Starting with this code:
TABLE FILE CAR
  SUM 
    DEALER_COST 
  BY CAR
  ACROSS COUNTRY
  ON TABLE COLUMN-TOTAL
END


I would like to sort the Across Values by highest column-total.

The only way I found to do this is to make 2 passes like this:
-DEFAULT &ECHO=ALL
APP HOLD IBISAMP
SET ASNAMES=ON
TABLE FILE CAR
SUM DEALER_COST AS TTL
BY COUNTRY
ON TABLE HOLD AS TEMP FORMAT FOCUS INDEX COUNTRY
END
-RUN
JOIN COUNTRY IN CAR TO COUNTRY IN TEMP AS J0
END
TABLE FILE CAR
SUM DEALER_COST
BY CAR
ACROSS HIGHEST TTL NOPRINT
ACROSS COUNTRY
ON TABLE COLUMN-TOTAL
END


There's got to be a simpler way, yes?

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


WF 8.1.05 Windows
 
Posts: 333 | Location: Orlando, FL | Registered: October 17, 2006Report This Post
<Kathryn Henning>
posted
Hi Anatess,

Have you seen this document? The bottom section discusses why two passes are necessary, but the example eliminates the JOIN.

How to change ACROSS order sequence

Regards,

Kathryn
 
Report This Post
Danny-SRL
Virtuoso
posted Hide Post
Anatess,

You will have to generate the column-total before you issue the ACROSS. This means that a HOLD file is necessary.
Here is another solution (without the JOIN), using a multi-set request:
  
TABLE FILE CAR
SUM DEALER AS TDEALER
BY COUNTRY
SUM DEALER
BY COUNTRY
BY CAR
ON TABLE SET ASNAMES ON
ON TABLE HOLD
END
TABLE FILE HOLD
SUM DEALER
BY CAR
ACROSS HIGHEST TDEALER NOPRINT
ACROSS COUNTRY
ON TABLE COLUMN-TOTAL
END


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Anatess
Guru
posted Hide Post
You guys rock. I have modified the code without the JOIN. It didn't have observable differences in response time but I'm sure it is much better internally.

Thanks!


WF 8.1.05 Windows
 
Posts: 333 | Location: Orlando, FL | Registered: October 17, 2006Report 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     [SOLVED]ACROSS HIGHEST TOTAL

Copyright © 1996-2020 Information Builders