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     Distinct Count in a subhead

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Distinct Count in a subhead
 Login/Join
 
RB
Gold member
posted
Hi,
I would like to display the distinct count of a field in subheading. Does any one has ideas on how to do this?

Thanks.
 
Posts: 76 | Registered: October 28, 2003Report This Post
j.gross
Virtuoso
posted Hide Post
CNT.DST.itemkey, as a verb object, gives the count. Not sure you can imbed such a construct in a header/footer, as " <CNT.DST.itemkey " If not, substitute a COMPUTEDd field, like so:

TABLE ...
SUM CNT.DST.itemkey NOPRINT
COMPUTE distinct/I5C= CNT.DST.itemkey; NOPRINT
BY XXX
BY YYY
BY ZZZ
ON XXX SUBHEAD
"... <distinct ..."
END
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Leah
Virtuoso
posted Hide Post
The DST operator is picky in the sense that is only on SUM or WRITE verbs and it must be at the lowest level on the aggregation chain.
So this works

SET STYLEMODE= FIXED
TABLE FILE CAR
SUM CNT.DST.MODEL
BY COUNTRY
END

but this doesn't
SET STYLEMODE= FIXED
TABLE FILE CAR
SUM CNT.DST.MODEL
PRINT COUNTRY MODEL
BY COUNTRY
END

0 ERROR AT OR NEAR LINE 28 IN PROCEDURE _ADHOCRQFOCEXEC *
(FOC1867) DST OPERATOR MUST BE AT THE LOWEST LEVEL OF AGGREGATION


So for unique counts you may want to create a unique counter field and ensure the file is in the order wanted when creating.

This works
SET STYLEMODE= FIXED
TABLE FILE CAR
PRINT COUNTRY MODEL
COMPUTE CNTR/I4 = IF MODEL EQ LAST MODEL THEN 0 ELSE IF COUNTRY NE LAST COUNTRY
THEN 1 ELSE 1;
BY COUNTRY NOPRINT
BY MODEL NOPRINT
ON TABLE HOLD AS HOLD1
END
TABLE FILE HOLD1
SUM CNTR NOPRINT
BY COUNTRY
PRINT MODEL
BY COUNTRY
ON COUNTRY SUBHEAD
" "
"UNIQUE MODELS ARE <CNTR"
" "
END

Of course the car file doesn't have a lot of models and you may not want to use a hold file.
A define would work if the input file is in the order wanted.
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Report This Post
<WFUser>
posted
The simplest way that I can see to put the number of unique instances in a subhead of a sort is to get that count and then join to it as follows:

TABLE FILE CAR
SUM CNT.DST.MODEL AS DSTMODS
BY COUNTRY
ON TABLE HOLD AS DSTMODS FORMAT FOCUS INDEX COUNTRY
END
JOIN COUNTRY IN CAR TO COUNTRY IN DSTMODS AS J1
TABLE FILE CAR
PRINT BODY MODEL CAR
BY COUNTRY
ON COUNTRY SUBHEAD
"<COUNTRY HAS <DSTMODS MODELS"
END
 
Report This Post
RB
Gold member
posted Hide Post
Both solutions are great. Thank you very much guys!
 
Posts: 76 | Registered: October 28, 2003Report 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     Distinct Count in a subhead

Copyright © 1996-2020 Information Builders