I am looking for a way to return only the top X number of records within a group.
For example, I have two division with 400 customers each. I want to see only the top 10 customers (based on sales) for each division.
What can I add to the following ?
PRINT DIVISION CUSTOMER NET SALES
BY DIVISION NO PRINT BY NET_SALES NO PRINT
Thanks StephenThis message has been edited. Last edited by: Kerry,
June 25, 2004, 07:53 PM
Chris Boylan
Stephen,
You can use BY TOP n (or BY HIGHEST n), in this case BY TOP 10. If you need to summarize the NET_SALES value from multiple detail records, then this may require two passes.
Here's a sample from the CAR file:
-* FIRST CREATE SUMMARIZED DATA TABLE FILE CAR SUM SEATS BY COUNTRY BY CAR ON TABLE HOLD END -* NOW SORT SUMMARIZED DATA BY TOP RESULTS TABLE FILE HOLD PRINT CAR SEATS BY COUNTRY BY HIGHEST 2 SEATS NOPRINT END
For yours it would be:
TABLE FILE BLAH SUM NET_SALES BY DIVISION BY CUSTOMER ON TABLE HOLD END
TABLE FILE HOLD PRINT CUSTOMER NET_SALES BY DIVISION BY TOP 10 NET_SALES NOPRINT END
Hope that helps.
-Chris
p.s. - Edited to fix coding sample.
June 25, 2004, 08:36 PM
Chris Boylan
Or... if your "NET_SALES" value is already summarized, then you CAN use PRINT (not SUM):
PRINT CUSTOMER NET_SALES BY DIVISION BY TOP 10 NET_SALES NOPRINT
CAR file sample:
TABLE FILE CAR PRINT CAR SEATS BY COUNTRY BY TOP 1 SEATS NOPRINT END
Keep in mind that if there is a TIE, then you may get more than 10 CUSTOMERS on your report (more than 1 CAR in my example).
-CB
June 25, 2004, 08:57 PM
Stephen
Thanks - that worked great - i threw a HIGHEST in there before the TOP and it pulled exactly what I wanted.
June 25, 2004, 09:17 PM
Chris Boylan
quote:
Originally posted by Stephen: [qb] Thanks - that worked great - i threw a HIGHEST in there before the TOP and it pulled exactly what I wanted. [/qb]
Great! My original summarized example was off a bit so I went back and fixed it (heh heh, gotta love the EDIT THIS POST feature)
But actually "BY HIGHEST" and "BY TOP" should do exactly the same thing (they're synonyms in TABLE) - you shouldn't need both.
You can even throw in a RANKED if you want and get the numerical ranking of each record.
e.g. RANKED BY HIGHEST 10 NET_SALES NOPRINT
Try it... you'll like it. -CB
June 02, 2006, 04:20 PM
umesh
I am looking for a way to return only the Middle X number of records within a group.
June 02, 2006, 04:29 PM
dwf
Define middle x. The 10th thru 20th? Or some span of say 10 records for which there are an equal number of records above and below?
dwf
June 03, 2006, 12:13 AM
susannah
use the LIST verb instead of PRINT and it will put a line number in the first position. HOLD the result then PRINT * IF LIST FROM 10 TO 20 or whatever. "LIST" refers to variable E01
In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
June 03, 2006, 12:44 PM
umesh
Thanks, i will give a try.
June 04, 2006, 09:36 PM
umesh
HI, SUSAN, I am enconturing an error,
(FOC006) THE FORMAT OF THE TEST VALUE IS INCONSISTENT WITH FIELD FORMAT: 5;
TABLE FILE CAR SUM SALES BY HIGHEST TOTAL SALES NOPRINT BY CAR ON TABLE HOLD END -*TYPE &RECORDS; -SET &MIDDLE = &RECORDS /2 ; -*TYPE &MIDDLE; TABLE FILE HOLD LIST CAR SALES
ON TABLE HOLD AS HOLD01
END TABLE FILE HOLD01 PRINT LIST CAR SALES IF LIST EQ 5; END
June 05, 2006, 02:00 AM
Piipster
Remove the ';' after the 5. IF statements don't use ';'.
ttfn, kp
Access to most releases from R52x, on multiple platforms.
June 05, 2006, 09:48 AM
umesh
thanks Piipster , After removing ';' I was able to execute without error.
June 28, 2007, 02:32 PM
KK
I have a report where I need to show Top 10 Customers based on sales. I implemented that by Ranking by Highest 10.
Now I need to display 1)The top 10 customers' sales totals 2)The remaining customers' sales totals and 3)The total customers (top 10 + remaining customers) sales totals
For e.g if I have 50 customers in total, I need display totals for top 10, remaining 40 and total(i.e. 50) customers sales.
TABLE FILE TEST SUM
CURRENTSALES/D12C AS 'Sales' CURRENTBP/D12C AS 'BP'
RANKED AS 'RANK' BY TOTAL HIGHEST 10 CURRENTSALES NOPRINT BY NAME AS 'Customer' ON TABLE COLUMN-TOTAL AS 'TOTAL' END
Any ideas ?
Thanks.
Test - Webfocus 7.6.7 Prod - Webfocus 7.6.7 Win2003 Sql Server 2000 and 2008
June 28, 2007, 02:43 PM
mgrackin
KK,
There is a new feature in 7.6.x called OTHERS which will do exactly what you want.
Thanks!
Mickey
FOCUS/WebFOCUS 1990 - 2011
June 28, 2007, 02:45 PM
mgrackin
Here's an example directly from the IBI Documentation:
TABLE FILE EMPLOYEE PRINT CURR_SAL LAST_NAME BY HIGHEST 2 ED_HRS PLUS OTHER AS 'Others' END
I would like to use amper variables for column totals. I have to calculate Total GP% based on the Total Sales and cost. So others might not work for this case.
Do you have a sample code for this type of report.
Thank you, KK
Test - Webfocus 7.6.7 Prod - Webfocus 7.6.7 Win2003 Sql Server 2000 and 2008
June 29, 2007, 12:31 PM
mgrackin
KK,
I do not understnad why you want to use amper variables for column totals. Unfortunately I do not have a 7.6 install to give you a working example. I was only posting information that I knew of. I would assume that you can apply all normal COMPUTE, SUBTOTALS, RECOMPUTEs etc. to a request using PLUS OTHER and get the results you want.
Thanks!
Mickey
FOCUS/WebFOCUS 1990 - 2011
June 29, 2007, 12:54 PM
KK
Mickey,
I apologize, what I mentioned earlier is not intended for this topic.
When I add others its adding subfoot for every sort break. This is not what I want, I want remaining 40 customers sales total. And its giving me invalid characters like @. I am not sure what is wrong.
Thank you.
Test - Webfocus 7.6.7 Prod - Webfocus 7.6.7 Win2003 Sql Server 2000 and 2008
June 30, 2007, 05:30 PM
FrankDutch
KK
you have to this in two steps first create a hold file with the ranking in it then a define.
DEFINE FILE HOLD GRP=IF RANK GT 10 THEN 1 ELSE 2; RANK2=IF RANK GT 10 THEN 11 ELSE RANK; NAME2/A25=IF RANK GT 10 THEN 'OTHERS' ELSE NAME; END
TABLE FILE HOLD CURRENTSALES/D12C AS 'Sales' CURRENTBP/D12C AS 'BP' BY GRP NOPRINT SUBTOTAL MULTILINES BY RANK2 BY NAME2 ON TABLE COLUMN-TOTAL END
END
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
July 02, 2007, 11:26 AM
ira
create a dummy sort field for the top 10 to be 1 value and all others to be a different dummy sort value - thus effectively splitting them into distinct groups. this should help.
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
July 03, 2007, 09:59 AM
KK
Thank you all for your suggestions. I had a deadline so didn't get a chance to research on that and had to make the changes in the sql server stored procedure to return the desired results.
Frank, I will definitely try your approach.This message has been edited. Last edited by: KK,
Test - Webfocus 7.6.7 Prod - Webfocus 7.6.7 Win2003 Sql Server 2000 and 2008
June 23, 2011, 06:05 AM
dhibach
Adding on to some old posts. I am creating a top 10 with a line totalling all others. I found Frank's approach worked great, but I am wondering if the same output is possible without a Hold File.
I have tinkered with the PLUS OTHER functionality from the GUI but so far I have not been successful.