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     Some questions on sorting and grouping in reports

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Some questions on sorting and grouping in reports
 Login/Join
 
Member
posted
Hi
I've written next focus code:

TABLE FILE WF_1_TRANSACTION
SUM
'CNT.WF_1_TRANSACTION.WF_1_TRANSACTION.TRANS_ID'
AS 'COUNT,TRANS_ID'
'WF_1_COMPANY.WF_1_COMPANY.NAME'
BY 'WF_1_TX_TYPE.WF_1_TX_TYPE.TYPE_NAME'
BY TOTAL HIGHEST 10 'CNT.WF_1_TRANSACTION.WF_1_TRANSACTION.TRANS_ID'
AS 'COUNT,TRANS_ID'
BY 'WF_1_COMPANY.WF_1_COMPANY.ID'
END


End the output result was:

TYPE_NAME | COUNT TRANS_ID | ID | COUNT TRANS_ID | NAME
Availability searches 256 29 256 Promess Ltd
261 44 261 Intapeople-IT Recruitment
269 31 269 Quayline Digital
283 30 283 Firesafe Technical
289 11 289 Vented Services Ltd
64 26 64 Intec Enquiry Agency
95 28 95 Donberg Electronics Ltd
100 46 100 Barnyard Pine
103 9 103 Ethos Corporation Ltd
107 21 107 James Hartley Ltd
Carriers Tracked 279 29 279 Promess Ltd
281 18 281 Craft Metal Products
31 281 Quayline Digital
306 11 306 Vented Services Ltd
...


Why the "COUNT TRANS_ID" column (namely values 256, 261, 269, 283, 289, 64, 95,...) is not sorted descending as i expect it should??

And some more newbie questions: why when I turn off the option "Qualified names" then if 2 tables (joined) have 2 identical column names, webfocus can choose not the right one and hence the report will be wrong?
And why qualified names are something like WF_1_TX_TYPE.WF_1_TX_TYPE.TYPE_NAME instead of
WF_1_TX_TYPE.TYPE_NAME.

Thanks. Your help is very appreciated.


-----------------------------------------
WF 7.1.3, Win XP, Tomcat
 
Posts: 8 | Registered: November 03, 2006Report This Post
Expert
posted Hide Post
Hi xonixx,

The following information may be of interest:

quote:
Why the "COUNT TRANS_ID" column (namely values 256, 261, 269, 283, 289, 64, 95,...) is not sorted descending as i expect it should??
Remove the TOTAL from BY TOTAL HIGHEST 10 'CNT.WF_1_TRANSACTION.WF_1_TRANSACTION.TRANS_ID'


quote:
And some more newbie questions: why when I turn off the option "Qualified names" then if 2 tables (joined) have 2 identical column names, webfocus can choose not the right one and hence the report will be wrong?
That is correct, and that is why we have fully qualified names.


quote:
And why qualified names are something like WF_1_TX_TYPE.WF_1_TX_TYPE.TYPE_NAME instead of
WF_1_TX_TYPE.TYPE_NAME.
We use the filename.segment name.fieldname to prevent the selection of an incorrect field.


Hope this helps. Smiler

Cheers,

Kerry


Kerry Zhan
Focal Point Moderator
Information Builders, Inc.
 
Posts: 1948 | Location: New York | Registered: November 16, 2004Report This Post
Member
posted Hide Post
Thank you for help!

quote:

Remove the TOTAL from BY TOTAL HIGHEST 10 'CNT.WF_1_TRANSACTION.WF_1_TRANSACTION.TRANS_ID'


But isn't TOTAL connected with CNT. ? Because when I remove it then in drop-down list for BY I've got only BY and ALL instead of SUM, CNT, etc..

Have one more question.

TABLE FILE HOLD1
PRINT
'HOLD1.HOLD1.NAME'
'HOLD1.HOLD1.TRANS_ID' AS 'count of tx for the type'
BY 'HOLD1.HOLD1.TYPE_NAME' AS 'TX_TYPE_NAME'
BY HIGHEST 10 'HOLD1.HOLD1.TRANS_ID' NOPRINT
END


I want to limit the number of records in each group to 10. But when there are equal TRANS_ID numbers in the group - the number of records increased so the number of records variates from 10 to 12 and more. How can I eliminate this to have exactly 10 records?

Thanks.


-----------------------------------------
WF 7.1.3, Win XP, Tomcat
 
Posts: 8 | Registered: November 03, 2006Report This Post
Expert
posted Hide Post
Hi xonixx,

This code will produce the report below:
TABLE FILE EMPDATA
PRINT   LN   
BY DEPT
BY HIGHEST 3 SALARY
ON TABLE SET ONLINE-FMT STANDARD
END


PAGE 1


DEPT SALARY LASTNAME
---- ------ --------
ACCOUNTING $83,000.00 SANCHEZ
$79,000.00 SOPENA
$62,500.00 WANG
ADMIN SERVICES $30,800.00 MORAN
$25,400.00 GORDON
CONSULTING $49,500.00 WANG
$40,900.00 WHITE
$35,900.00 ELLNER
CUSTOMER SUPPORT $62,500.00 WHITE <====== note two records, different names same salary
HIRSCHMAN
$54,100.00 FERNSTEIN
$19,300.00 RUSSO
MARKETING $62,500.00 BELLA
ADAMS
CVEK
$58,800.00 GOTLIEB
$55,500.00 VALINO
PATEL
PERSONNEL $80,500.00 NOZAWA
$45,000.00 GRAFF
$33,300.00 KASHMAN
PROGRAMMING & DVLPMT $49,500.00 ROSENTHAL
$49,000.00 MARTIN
$42,900.00 CASTALANETTA
SALES $115,000.00 LASTRA
$70,000.00 CASSANOVA
$54,100.00 ADDAMS


This may be closer to what you want:
TABLE FILE EMPDATA
PRINT   SALARY  
BY DEPT
BY HIGHEST 3 SALARY
ON TABLE SET ONLINE-FMT STANDARD
WHERE TOTAL SALARY NE LAST SALARY
END



PAGE 1


DEPT SALARY SALARY
---- ------ ------
ACCOUNTING $83,000.00 $83,000.00
$79,000.00 $79,000.00
$62,500.00 $62,500.00
ADMIN SERVICES $30,800.00 $30,800.00
$25,400.00 $25,400.00
CONSULTING $49,500.00 $49,500.00
$40,900.00 $40,900.00
$35,900.00 $35,900.00
CUSTOMER SUPPORT $62,500.00 $62,500.00
$54,100.00 $54,100.00
$19,300.00 $19,300.00
MARKETING $62,500.00 $62,500.00
$58,800.00 $58,800.00
$55,500.00 $55,500.00
PERSONNEL $80,500.00 $80,500.00
$45,000.00 $45,000.00
$33,300.00 $33,300.00
PROGRAMMING & DVLPMT $49,500.00 $49,500.00
$49,000.00 $49,000.00
$42,900.00 $42,900.00
SALES $115,000.00 $115,000.00
$70,000.00 $70,000.00
$54,100.00 $54,100.00



Hope it helps. Smiler

Cheers,

Kerry


Kerry Zhan
Focal Point Moderator
Information Builders, Inc.
 
Posts: 1948 | Location: New York | Registered: November 16, 2004Report This Post
Member
posted Hide Post
Thank you again!

Your examples work, but unfortunately I need slighty differnent. In your case rows where
WHERE TOTAL SALARY NE LAST SALARY
simply not displayed. I need them to display!
Let me describe what I need.
Say, I have a table with 2 columns: name and age. For example I need to display 10 oldest persons, sorting them by their ages descending.

If use such code

TABLE FILE A
PRINT name
BY HIGHEST 10 age
END

then if it is 2 persons with the same age - ... They will be grouped. How to solve this problem? Or may be there is a different approach?
Thanks.

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


-----------------------------------------
WF 7.1.3, Win XP, Tomcat
 
Posts: 8 | Registered: November 03, 2006Report This Post
Expert
posted Hide Post
Hi xonixx,

The problem is that you want to create two unrelated groups:
1) 10 ages
2) but you may have 12 people (two with the same age) , Since names will be printed and you want only 10 lines of output, which name(s) do you want to lose?

Both examples were given earlier. If you need additional assistance, please contact Information Builders' Customer Support Services and open a case for it, then we can do a desktop streaming session with you. You may either call at 1-800-736-6130, or access the online system InfoResponse at http://techsupport.ibi.com. Here is a list of information to be ready when you call: http://techsupport.ibi.com/before_you_call.jsp.

Hope this helps.

Cheers,

Kerry


Kerry Zhan
Focal Point Moderator
Information Builders, Inc.
 
Posts: 1948 | Location: New York | Registered: November 16, 2004Report This Post
Member
posted Hide Post
I need something like

num name age

1 Peter 67
2 Ann 55
3 Kate 47 <-- equal
4 John 47 <--+
5 Sam 45
6 Bill 35 <-- here too
7 Terry 35 <--+
8 Roger 35 <--+
9 Patrick 27
10 Tom 25

How can I achieve this?
I wonder is it posible to sort by column without grouping by it?
Thanks.


-----------------------------------------
WF 7.1.3, Win XP, Tomcat
 
Posts: 8 | Registered: November 03, 2006Report This Post
Guru
posted Hide Post
The code in your initial post looks correct, I'm just not sure why all the single quote marks are there around the field names. Shouldn't be a problem. . Was that generated by a GUI tool?

If you are not getting the top 10 values in descending order I'd want to see some sort of traces to figure out what is happening... if this persists, I'd open a case with the Tech Support site.


ttfn, kp


Access to most releases from R52x, on multiple platforms.
 
Posts: 346 | Location: Melbourne Australia | Registered: April 15, 2003Report This Post
Guru
posted Hide Post
... and the usual question... Focus or WebFOCUS? What release?


ttfn, kp


Access to most releases from R52x, on multiple platforms.
 
Posts: 346 | Location: Melbourne Australia | Registered: April 15, 2003Report This Post
Member
posted Hide Post
The code is correct, it is geberated by the gui tool and it works just as expected. The problem is that I can't find the way to obtain 10 rows rather than 10 groups (that can be definitely more then 10 rows)

I'm using WebFocus 7.1.3


-----------------------------------------
WF 7.1.3, Win XP, Tomcat
 
Posts: 8 | Registered: November 03, 2006Report This Post
Platinum Member
posted Hide Post
Could you do something like:

TABLE FILE MYDATA
PRINT
NAME
COMPUTE CNTR/I9=1 + LAST CNTR;
BY HIGHEST 10 AGE
WHERE TOTAL CNTR LT 11;
END


Tewy


WF 7.6.11
Output: HTML, PDF, Excel
 
Posts: 123 | Location: UK | Registered: October 09, 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     Some questions on sorting and grouping in reports

Copyright © 1996-2020 Information Builders