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.

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.

Cheers,

Kerry

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.

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.

Cheers,

Kerry

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,

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

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.

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.

... and the usual question... Focus or WebFOCUS? What release?

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

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