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     [CLOSED] Count Distinct within - 8206

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] Count Distinct within - 8206
 Login/Join
 
Platinum Member
posted
I have a report that does a CNT.DST.FieldName within a BY field -- it was created in the GUI in 8203 but it won't work in 8206.

Here's the code:
 
TABLE FILE ENTERPRISE_ANALYTICS/MST_POLICY_ITEMS_PREMIUM
SUM CNT.DST.MST_POLICY_ITEMS_PREMIUM.MST_POLICY_ITEMS_PREMIUM.POLICY_NUMBER AS 'Policy Count'
CNT.DST.MST_POLICY_ITEMS_PREMIUM.MST_POLICY_ITEMS_PREMIUM.POLICY_NUMBER WITHIN MST_POLICY_ITEMS_PREMIUM.MST_POLICY_ITEMS_PREMIUM.STATE_ABBR NOPRINT
COMPUTE c_percent_of_total/D12%=C1/C3 * 100 ; AS '% of Total'
MST_POLICY_ITEMS_PREMIUM.MST_POLICY_ITEMS_PREMIUM.GROSS_AMOUNT/P20M AS 'CMP DWP'
COMPUTE c_percent_prem/D12%=PCT.MST_POLICY_ITEMS_PREMIUM.MST_POLICY_ITEMS_PREMIUM.GROSS_AMOUNT ; AS '% of Total'
DST.J002.MST_POLICY_INFORMATION_ACTIVE.POLICY_TOTAL_INSURED_VALUE/P20M AS 'TIV'
COMPUTE c_percent_tiv/D12%=PCT.J002.MST_POLICY_INFORMATION_ACTIVE.POLICY_TOTAL_INSURED_VALUE ; AS '% of Total'
BY MST_POLICY_ITEMS_PREMIUM.MST_POLICY_ITEMS_PREMIUM.STATE_ABBR NOPRINT
BY d_county_group AS 'County Group'
WHERE MST_POLICY_ITEMS_PREMIUM.MST_POLICY_ITEMS_PREMIUM.NEW_DIRECT_WRITTEN_PREMIUM_FLAG_YTD EQ 1;
WHERE MST_POLICY_ITEMS_PREMIUM.MST_POLICY_ITEMS_PREMIUM.HOLD_FLAG EQ 0;
WHERE MST_POLICY_ITEMS_PREMIUM.MST_POLICY_ITEMS_PREMIUM.POLICY_TYPE EQ 'CMP';
WHERE MST_POLICY_ITEMS_PREMIUM.MST_POLICY_ITEMS_PREMIUM.STATE_ABBR EQ 'LA';
WHERE MST_POLICY_ITEMS_PREMIUM.MST_POLICY_ITEMS_PREMIUM.DIRECT_WRITTEN_PREMIUM_ACTIVE_DATE GE J003.MAINT_REPORTING_DATES.BEGINNING_OF_LAST_YEAR;
ON TABLE PCHOLD FORMAT HTML
 


The error it gives me in 8206 is FOC1867 - Operator must be at the lowest level of aggregation. I found this post post in focal point, but when I try to create a compute field for CNT.DST.MST_POLICY_ITEMS_PREMIUM.MST_POLICY_ITEMS_PREMIUM.POLICY_NUMBER I get a syntax error.

Anyone know of another work around or is this a case for tech support?

Thanks,
Deb

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


WebFOCUS 8.2.03 (production), 8.2.06 (testing)
AppStudio, InfoAssist
Windows, All Outputs
 
Posts: 183 | Location: Indiana | Registered: December 05, 2017Report This Post
Member
posted Hide Post
quote:
FOC1867 - Operator must be at the lowest level of aggregation

we have the same issue when i looked in the techsupport this is what i found.

This is working as designed. A WITHIN is the alternative to a multi verb request which allows summarization of values at different sort levels. The error message is indicating that DST must be at the lowest sort level only.

Please refer to the following link:
https://webfocusinfocenter.inf...s6/wf8206crlang.pdf, pages 70 - 72

The following error occurs if you use a multi-verb request, SUM DST.fieldname BY field PRINT fld BY fld (a verb object operator used with the SUM command must be at the lowest level of aggregation):
(FOC1867) DST OPERATOR MUST BE AT THE LOWEST LEVEL OF AGGREGATION


WebFOCUS 8.2.04
AIX/UNIX/Windows, All Outputs
 
Posts: 20 | Registered: November 17, 2015Report This Post
Platinum Member
posted Hide Post
Curious. I guess this is code tightening between 8203 and 8206?


WebFOCUS 8.2.03 (production), 8.2.06 (testing)
AppStudio, InfoAssist
Windows, All Outputs
 
Posts: 183 | Location: Indiana | Registered: December 05, 2017Report This Post
Master
posted Hide Post
Still on 8008 here and it has always worked that way for me. This is another one of my pet peeves. Maybe we can get one of the Walters to explain why it works this way. The workaround is to do each count distinct in a separate TABLE and hold the files then use MATCH or JOIN to put the data into one file, at least, that is what I have always had to do.


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
Platinum Member
posted Hide Post
My business users are gonna be so thrilled to have to rewrite their reports to use hold tables and joins! Razzer

I guess that's what they will have to do because I can't seem to get any other trick to work.


WebFOCUS 8.2.03 (production), 8.2.06 (testing)
AppStudio, InfoAssist
Windows, All Outputs
 
Posts: 183 | Location: Indiana | Registered: December 05, 2017Report This Post
Master
posted Hide Post
One of my other pet peeves is Count DST does not work in MATCH. Try it and you get (FOC1854) THE DST OPERATOR IS ONLY SUPPORTED IN TABLE REQUESTS. I which someone could explain that one as well.


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
Virtuoso
posted Hide Post
I did solicit "The Walters" as John called them and this is the reply..

Even though there was no error prior to 8206, the behavior gave you the sum of the distinct values at the lowest level rather than the actual distinct values for the BY grouping. Look at this code:

-* Raw data
TABLE FILE CAR
PRINT SEATS
BY COUNTRY
BY CAR
END

-* Distinct for one BY
TABLE FILE CAR
SUM CNT.DST.SEATS
BY COUNTRY
END

-* Distinct for two BYs NOTE for ENGLAND it is 3.
TABLE FILE CAR
SUM CNT.DST.SEATS
BY COUNTRY
BY CAR
END

-* Distinct for two BYs with WITHIN FIRST BY
-* NOTE ENGLAND now is 4 because
-* You have 2 distinct seats for JAGUAR
-* 1 for JENSEN and 1 for TRIUMPH. The SUM WITHIN
-* simply adds them up which might not be what
-* the customer really wanted (meaning they wanted
-* to see the 3 from the previous request with one BY phrase
TABLE FILE CAR
SUM CNT.DST.SEATS WITHIN COUNTRY
BY COUNTRY
BY CAR
END

In order to avoid not getting what is expected, the FOC1867 is thrown.


Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
 
Posts: 2127 | Location: Customer Support | Registered: April 12, 2005Report This Post
Platinum Member
posted Hide Post
Thanks Chuck, but for the way we've been using it, it has been correct -- we use it to get a percent of a sublevel within the greater level. We are looking to answer questions like: What percentage of cars have 2 types of seat configurations out of all cars in England (to use the CAR example)?
It is just a curious thing that it has been working for us when apparently it shouldn't. Thanks for checking with The Walters!


WebFOCUS 8.2.03 (production), 8.2.06 (testing)
AppStudio, InfoAssist
Windows, All Outputs
 
Posts: 183 | Location: Indiana | Registered: December 05, 2017Report 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     [CLOSED] Count Distinct within - 8206

Copyright © 1996-2020 Information Builders