Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Count Distinct within - 8206
Go
New
Search
Notify
Tools
Reply
  
[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: 142 | Location: Indiana | Registered: December 05, 2017Reply With QuoteReport 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: 12 | Registered: November 17, 2015Reply With QuoteReport 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: 142 | Location: Indiana | Registered: December 05, 2017Reply With QuoteReport 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: 942 | Location: Oklahoma City | Registered: October 27, 2006Reply With QuoteReport 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: 142 | Location: Indiana | Registered: December 05, 2017Reply With QuoteReport 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: 942 | Location: Oklahoma City | Registered: October 27, 2006Reply With QuoteReport 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: 1693 | Location: Customer Support | Registered: April 12, 2005Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Count Distinct within - 8206

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.