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.
Given the VAL column how do I achieve this kind of ranking through the WebFOCUS function 'RANK' available ? By default, the RANK functions as follows: R1 450 1 R2 300 2 R3 300 2 R4 250 3 R5 250 3 R6 250 3 R7 250 3 R8 200 4
by using: TABLE FILE <> PRINT DEPT Ranked by VAL END
Using WF 7.1.7/Dev Studio
Posts: 189 | Location: Boston, MA | Registered: July 12, 2005
Yes the coding is basically correct except for not using uppercase. But if you want the output in the format as printed (with the rank code in the third position you should do this. I assume the field "rank" is not a real database field.
TABLE FILE WHATEVER
PRINT DEPT VAL
RANKED BY VAL
ON TABLE HOLD
END
TABLE FILE HOLD
PRINT DEPT VAL RANK
BY RANK NOPRINT
BY DEPT NOPRINT
END
Would you be so kind to change your signatur so we know which version you are using
Frank
prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006
TABLE FILE Fn
PRINT
DEPT
RANKED BY VAL
ON TABLE SET STYLE *
TYPE=REPORT,COLUMN=N2,SEQUENCE=1,$
TYPE=REPORT,COLUMN=N3,SEQUENCE=2,$
TYPE=REPORT,COLUMN=N1,SEQUENCE=3,$
ENDSTYLE
END
Alan. WF 7.705/8.007
Posts: 1451 | Location: Portugal | Registered: February 07, 2007
TABLE FILE CAR
PRINT
CAR
RETAIL_COST
AND COMPUTE ROWNUM/I9 = LAST ROWNUM + 1;
BY HIGHEST RETAIL_COST NOPRINT
ON TABLE HOLD
END
-RUN
TABLE FILE HOLD
PRINT
CAR
RETAIL_COST
AND COMPUTE MYRANK/I9 = IF RETAIL_COST EQ LAST RETAIL_COST THEN LAST MYRANK ELSE ROWNUM; AS 'RANK'
END
In this example I am not doing any summing but if you do and depending on your RDBMS this could be faster.
Okay, The Sequence option for the style sheet is available in the GUI.
Select the Options Button, Select Custom field placement, press OK and then drag the columns where you want them. Works good.
Performance. Perhaps this isn't fair, the SEQUENCE approach does not go through a HOLD file. I ran various tests (36 in all) on 15,000, 150,000 and 1,500,000 records, different orders and under restarted servers and under new agents.
From a base mark of the approach TS used: Frank's method - 4% saving CPU, no noticeable saving for I/O, 7% saving response time. My method - 9% saving CPU, 92% saving for I/O, 14% saving response time.
Which goes to prove that HOLD files eat I/O!
Alan. WF 7.705/8.007
Posts: 1451 | Location: Portugal | Registered: February 07, 2007
I had posted this a couple years ago for the ranking and using only 1 table call.. although the person was looking for a TOP 100 report where there were only 100 entries and not more because of ties.
So for this question, getting "actual ranking" and having Rank in the 3rd column would be:
TABLE FILE TNAME
PRINT COMPUTE RANKNUM/I6 = RANKNUM + 1; NOPRINT
DEPT VAL
COMPUTE RANK/I6 = IF VAL EQ LAST VAL THEN LAST RANK ELSE RANKNUM;
BY HIGHEST VAL NOPRINT
END
This message has been edited. Last edited by: Leo L,
Prod: WebFOCUS 7.6.4 - Self Service - Windows Server2003 - Apache Tomcat 5.5 Dev: WebFOCUS 7.6.4 - Self Service - Windows XP SP2 - Apache Tomcat 5.5