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.



Read-Only Read-Only Topic
Go
Search
Notify
Tools
COUNT help
 Login/Join
 
Gold member
posted
hi Smiler
i'm having a little problem with my counts.
this is the code:


DEFINE FILE CAR
CNTR/I1=1;
END

TABLE FILE CAR
PRINT CAR
BY COUNTRY
BY MODEL


ON COUNTRY SUBFOOT
"<ST.CNTR"
END


right now, it's showing me the total count of the MODEl per each country, but i want to see the count for each CAR per country (keeping the same sort order)

help please

Thanks!


~~~||~~~||~~~||~~~||~~~||~~~||~~~||~~~||~~~||~~~
PROD: WebFOCUS 7.1.3 on Win 2K/IIS 6/ISAPI Mode/Self-Serve Apps Only (No App Server)
TEST: WebFOCUS 7.1.3 on Win 2K/IIS 6/Weblogic 8.1/Servlet Mode
 
Posts: 74 | Location: Gaithersburg, MD | Registered: August 08, 2006Report This Post
Virtuoso
posted Hide Post
quote:
DEFINE FILE CAR
CNTR/I1=1;
END

TABLE FILE CAR
PRINT CAR
BY COUNTRY
BY MODEL


ON COUNTRY SUBFOOT
"END


what is the difference?

you see 4, 1, 4, 2 , 7
thats the number of cars you have but also the number of models.

Frank




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
Gold member
posted Hide Post


ok, i dont know if i explained myself incorrectly... if you look at Italy, there's two types of cars, and 4 types of models.
on the subfoot count it says "4", but i want it to say "2"......

so i want to count the cars, not the models


~~~||~~~||~~~||~~~||~~~||~~~||~~~||~~~||~~~||~~~
PROD: WebFOCUS 7.1.3 on Win 2K/IIS 6/ISAPI Mode/Self-Serve Apps Only (No App Server)
TEST: WebFOCUS 7.1.3 on Win 2K/IIS 6/Weblogic 8.1/Servlet Mode
 
Posts: 74 | Location: Gaithersburg, MD | Registered: August 08, 2006Report This Post
<JG>
posted
COMPUTE not DEFINE.

You are working at summary level not detail.

TABLE FILE CAR
PRINT
COMPUTE CNTR/I1= IF CAR EQ LAST CAR THEN 0 ELSE 1; NOPRINT
CAR
BY COUNTRY
BY CAR NOPRINT
BY MODEL
ON COUNTRY SUBFOOT
 "<ST.CNTR"
 

END

Sorry had toi edit because of the tag.
 
Report This Post
Expert
posted Hide Post
This may do the trick if you're looking for a count of unique cars per country:

DEFINE FILE CAR
CNTR/I9 = IF CAR EQ LAST CAR THEN CNTR + 1 ELSE 1;
END

TABLE FILE CAR
SUM
CAR
BY COUNTRY

ON COUNTRY
SUBFOOT
"<ST.CNTR"
SUM
CAR
BY COUNTRY
BY MODEL
END


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
Gold member
posted Hide Post
i knew that!
hehe

Thanks!!!



~~~||~~~||~~~||~~~||~~~||~~~||~~~||~~~||~~~||~~~
PROD: WebFOCUS 7.1.3 on Win 2K/IIS 6/ISAPI Mode/Self-Serve Apps Only (No App Server)
TEST: WebFOCUS 7.1.3 on Win 2K/IIS 6/Weblogic 8.1/Servlet Mode
 
Posts: 74 | Location: Gaithersburg, MD | Registered: August 08, 2006Report This Post
Virtuoso
posted Hide Post
The problem is the Define and the BY. You define CNTR as 1 which means that for every line that appears the value is 1. Then you sort by count and by model. Since you sort by model it forces a line for each model line and you get 4 instead of 3.
If you do a multiverb set then you get what you want because it first calculates counter by car then prints all the models.
DEFINE FILE CAR
CNTR/I1 WITH CAR=1;
END
TABLE FILE CAR
SUM CNTR NOPRINT
BY COUNTRY
SUM CAR
BY COUNTRY
BY MODEL
ON COUNTRY SUBFOOT
"END


Regards,

Darin



In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Report This Post
<JG>
posted
Someone should really explain to the world the difference between

DEFINE and COMPUTE.

DEFINE works on the input row level.

COMPUTE works on the report matrix level.

It’s something that seems to be forgotten quite frequently.

Prime examples if the averages are totally screwy I bet you’re
summing them and not calculating them at the correct point.
 
Report This Post
Virtuoso
posted Hide Post
I love this list. It shows so many ways to solve problems. Only issue with define that I can see is if the incoming file is not in the order you are doing your define with. I've been bit by that one. Added the computed item as other wise the subfoot is running total for some reason.
FUNKY BUT WORKS

TABLE FILE CAR
SUM CNT.CAR NOPRINT
COMPUTE CARCNT/I4 = C1; NOPRINT
BY COUNTRY
PRINT CAR
BY COUNTRY
BY MODEL

ON COUNTRY SUBFOOT
"CAR COUNT FOR COUNTRY <COUNTRY IS <CARCNT"
END


Leah
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Report This Post
<JG>
posted
Not FUNKY,

That's the point.

The ST. in my example was actually not needed because a report matrix item
(i.e. COMPUTE) is by definition a total at it's level.

DEFINE / COMPUTE

A bit of training, a lot of frustration, a lot of practice.

The basic rules work 'almost every time', BUT we all forget.
 
Report This Post
Platinum Member
posted Hide Post
The CAR file is a Hierarchical data base. The value of a record count will vary with it position in the data base. The “with” in define tells FOCUS where in the structure you want the defined field. This show the result of using with for different position in a data base. The multi-verb allows you to see the result in a single table request.

DEFINE FILE CAR
CTR_COUNTRY/I3 WITH COUNTRY = 1;
CTR_CAR/I3 WITH CARS = 1;
CTR_MODEL/I3 WITH MODEL = 1;
END

TABLE FILE CAR
SUM
CTR_COUNTRY AS 'CTR,COUNTRY'
BY COUNTRY

SUM CTR_CAR AS 'ctr,car'
BY COUNTRY
BY CARS

SUM CTR_MODEL AS 'ctr,model'
BY COUNTRY
BY CARS
BY MODEL
ON TABLE SUMMARIZE
END
The output:

PAGE 1


COUNTRY CTR
COUNTRY
CAR ctr
car
MODEL ctr
model
ENGLAND 1 JAGUAR 1 V12XKE AUTO 1
XJ12L AUTO 1
JENSEN 1 INTERCEPTOR III 1
TRIUMPH 1 TR7 1
FRANCE 1 PEUGEOT 1 504 4 DOOR 1
ITALY 1 ALFA ROMEO 1 2000 4 DOOR BERLINA 1
2000 GT VELOCE 1
2000 SPIDER VELOCE 1
MASERATI 1 DORA 2 DOOR 1
JAPAN 1 DATSUN 1 B210 2 DOOR AUTO 1
TOYOTA 1 COROLLA 4 DOOR DIX AUTO 1
W GERMANY 1 AUDI 1 100 LS 2 DOOR AUTO 1
BMW 1 2002 2 DOOR 1
2002 2 DOOR AUTO 1
3.0 SI 4 DOOR 1
3.0 SI 4 DOOR AUTO 1
530I 4 DOOR 1
530I 4 DOOR AUTO 1
TOTAL 5 10 18


Note the counter vary are 5, 10, and 18. These values may vary with the records in the CAR file.


Jim Morrow
Web Focus 7.6.10 under Windows 2003
MVS 7.3.3



 
Posts: 129 | Registered: June 01, 2005Report This Post
Platinum Member
posted Hide Post
Opps!! Sorry the report is gibberish. Had HTML disabled.

PAGE 1

COUNTRY
CTR
COUNTRY

CAR
ctr
car

MODEL
ctr
model
ENGLAND 1 JAGUAR 1 V12XKE AUTO 1
XJ12L AUTO 1
JENSEN 1 INTERCEPTOR III 1
TRIUMPH 1 TR7 1
FRANCE 1 PEUGEOT 1 504 4 DOOR 1
ITALY 1 ALFA ROMEO 1 2000 4 DOOR BERLINA 1
2000 GT VELOCE 1
2000 SPIDER VELOCE 1
MASERATI 1 DORA 2 DOOR 1
JAPAN 1 DATSUN 1 B210 2 DOOR AUTO 1
TOYOTA 1 COROLLA 4 DOOR DIX AUTO 1
W GERMANY 1 AUDI 1 100 LS 2 DOOR AUTO 1
BMW 1 2002 2 DOOR 1
2002 2 DOOR AUTO 1
3.0 SI 4 DOOR 1
3.0 SI 4 DOOR AUTO 1
530I 4 DOOR 1
530I 4 DOOR AUTO 1
TOTAL 5   10   18


Jim Morrow
Web Focus 7.6.10 under Windows 2003
MVS 7.3.3



 
Posts: 129 | Registered: June 01, 2005Report This Post
<JG>
posted
DEFINE is not the correct option for what is wanted for a very simple reason
It processes every input row, that means if you have 100k rows it performs 100k
calculations, that's a performance hit.
For efficiency these calculations should be done as a COMPUTE as that operates at output level.

In a multi-verb request you do not need to use WITH as the report matrix has already
been rolled up to the required level.


TABLE FILE CAR
SUM
COMPUTE CTR_COUNTRY/I3 = 1;AS 'CTR,COUNTRY'
BY COUNTRY

SUM
COMPUTE CTR_CAR/I3 = 1; AS 'ctr,car'
BY COUNTRY
BY CARS

SUM
COMPUTE CTR_MODEL/I3 = 1; AS 'ctr,model'
BY COUNTRY
BY CARS
BY MODEL
ON COUNTRY SUBTOTAL
END
 
Report This Post
Virtuoso
posted Hide Post
JG

Whilst you are correct that when using SUMmed values, a COMPUTE can be more efficient, in this case there is no difference in the number of calculations done between a DEFINE and a COMPUTE, as you are retrieving every value into the internal matrix anyway.

The WITH option triggers the DEFINE on retrieving every instance of that field, the COMPUTE is triggered on the Internal Matrix value for that field. For COUNTRY both are triggered 5 times.

There is no 'efficient' way of doing this, though for a FOCUS db using SUM CNT.field might just have an edge because of the way it builds an IM.
TABLE FILE CAR
SUM CNT.COUNTRY
SUM CNT.CAR
SUM CNT.MODEL
END

Or

TABLE FILE CAR
SUM CNT.COUNTRY
SUM CNT.CAR BY COUNTRY
SUM CNT.MODEL BY COUNTRY BY CAR
ON COUNTRY SUBTOTAL
END

This message has been edited. Last edited by: Alan B,


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic


Copyright © 1996-2020 Information Builders