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     Counting number of values in a group

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Counting number of values in a group
 Login/Join
 
Rob M.
Gold member
posted
What I am trying to do is count the number of stores in a district while still displaying the district and corresponding stores.

I want my report to look like this:

DISTRICT (BY field) --- STORE NUMBER (BY field) --- COUNT OF STORES IN DISTRICT

101 --- 0001 --- 4
101 --- 0002 --- 4
101 --- 0003 --- 4
101 --- 0004 --- 4
102 --- 0010 --- 2
102 --- 0011 --- 2
103 --- etc.......

I don't mind that it will list the counted number of stores next to each unique store number in the listing. (I actually need this for my final analysis in Excel).

I cannot figure out how to do this whatsoever. I tried messing with multiple sort fields but nothing worked. Can anyone help?


Rob M.
Target Corporation

WF 7.1.4
 
Posts: 73 | Location: Minneapolis, MN | Registered: August 08, 2007Report This Post
Francis Mariani
Expert
posted Hide Post
Try this:
SET HOLDLIST   = PRINTONLY
SET ASNAMES    = ON
SET HOLDFORMAT = ALPHA
SET BYDISPLAY  = ON
-RUN

TABLE FILE GGSTORES
SUM
CNT.STORE_CODE AS CNT_STORE
BY STATE
PRINT
STORE_NAME
BY STATE
ON TABLE HOLD AS H001
END
-RUN

TABLE FILE H001
PRINT 
STORE_NAME
CNT_STORE
BY STATE
END
-RUN


The result:
STATE STORE_NAME          CNT_STORE 
----- ------------------- ---------
CA    GOTHAM GRINDS #1040 2 
CA    GOTHAM GRINDS #1244 2 
CT    GOTHAM GRINDS #1100 1 
FL    GOTHAM GRINDS #1200 1 
GA    GOTHAM GRINDS #1041 1 
IL    GOTHAM GRINDS #1020 1 
MA    GOTHAM GRINDS #1044 1 
MO    GOTHAM GRINDS #1250 1 
NY    GOTHAM GRINDS #1109 1 
TN    GOTHAM GRINDS #1088 1 
TX    GOTHAM GRINDS #1019 1 
WA    GOTHAM GRINDS #1248 1


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Alan B
Virtuoso
posted Hide Post
Try something based on this example:
TABLE FILE CAR
SUM CNT.CAR WITHIN COUNTRY
BY COUNTRY
BY CAR
END


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Justin C
Member
posted Hide Post
Rob,

I work the same data you do regularly (same company). Don't hesitate to contact me if you continue to have problems. Depending on how your data is stored you may need to tell WebFocus to only return distinct store values.

TABLE FILE HOLDLOCI
SUM
CNT.DST.LOC WITHIN DIST AS 'LOC,COUNT'
BY REGION
BY GROUP
BY DIST
ON TABLE SET PAGE-NUM OFF
ON TABLE COLUMN-TOTAL AS 'TOTAL'
ON TABLE PCHOLD FORMAT HTML

Justin.Costa@target.com


WF 7.1.4 on WIN NT
 
Posts: 4 | Registered: July 25, 2007Report This Post
Rob M.
Gold member
posted Hide Post
quote:
Originally posted by Alan B:
Try something based on this example:
TABLE FILE CAR
SUM CNT.CAR WITHIN COUNTRY
BY COUNTRY
BY CAR
END


This worked GREAT!

Thanks so much!


Rob M.
Target Corporation

WF 7.1.4
 
Posts: 73 | Location: Minneapolis, MN | Registered: August 08, 2007Report This Post
jgelona
Master
posted Hide Post
This reminds me of Name That Tune. LOL.

Instead of, "I can name that tune in 5 notes." I'm seeing "I can solve that problem in 8 lines of code." The next person says, "I can solve that problem in 5 lines of code."


In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
 
Posts: 975 | Location: Oklahoma City | Registered: October 27, 2006Report This Post
FrankDutch
Virtuoso
posted Hide Post
how about one line of code?

TABLE FILE CAR SUM CNT.CAR WITHIN COUNTRY BY COUNTRY BY CAR
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

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 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     Counting number of values in a group

Copyright © 1996-2020 Information Builders