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] Not able to get a distinct count on a field
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] Not able to get a distinct count on a field
 Login/Join
 
Gold member
posted
I'm trying to count the number of distinct orders I get in this result set, but I think it's counting the number of lines, not the number of orders.

 TABLE FILE SHIP2LIST
-*BY 'HOME_BR' AS 'BRANCH'
-*ON TABLE HOLD AS USXTRACT FORMAT FOCUS INDEX 'DIVISION' 'J1.PRODUCTS_US.PROD_CAT' 'J1.PRODUCTS_US.BRAND' 'J0.SALES_CURR_PREV_FY_US.GL_YR_MONTH' 
SUM 
     'CNT.DST.J0.SALES_CURR_PREV_FY_US.ORDER_NUM' WITHIN TABLE
	 UNITS_PURCH AS 'U_PURCHASED'
     'J0.SALES_CURR_PREV_FY_US.PURCH_DOLLARS' AS 'P_DOLLARS'
     UNITS_RETURNED AS 'U_RETURNED'
     'J0.SALES_CURR_PREV_FY_US.RETURN_DOLLARS' AS 'R_DOLLARS'
     'J0.SALES_CURR_PREV_FY_US.GP_DOLLARS' AS 'GP_DOLLARS'
BY 'SHIP2LIST.SEG01.DIVISION' AS 'DIVISION'
BY 'J0.SALES_CURR_PREV_FY_US.GL_DT_FISCAL_YEAR' AS 'FYEAR'
BY 'J0.SALES_CURR_PREV_FY_US.GL_DT_FISCAL_QUARTER' AS 'FQTR'
BY 'J0.SALES_CURR_PREV_FY_US.GL_DT_FISCAL_PERIOD' AS 'FPER'
BY 'J0.SALES_CURR_PREV_FY_US.GL_YR_MONTH' AS 'YR_MO'
BY 'J0.SALES_CURR_PREV_FY_US.ORDER_METHOD' AS 'ORD_METH'
BY 'SHIP2LIST.SEG01.REGIONAL_MGR_ID' AS 'REG_MGR'
BY 'J0.SALES_CURR_PREV_FY_US.SALES_MGR_ID' AS 'SALES_MGR'
BY 'J0.SALES_CURR_PREV_FY_US.PR_BR' AS 'BRANCH'
BY 'SHIP2LIST.SEG01.BRANCH_ABBREV' AS 'BRANCH_NAME'
BY 'J0.SALES_CURR_PREV_FY_US.ORDER_NUM'
...MORE BY FIELDS...
.
.
.


I'm trying to build a summarized table that I can use in a dashboard environment and then I can do calculations for % of orders. I've tried a couple different scenarios and none seem to be working.

I don't actually need the ORDER_NUM to be a part of the final summarized table - I just have it in this part of the procedure to be able to count it. All I ultimately need is the # of orders.

Any help is greatly appreciated.

Jeralee

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


WebFOCUS 7.7.03
Linux / Universe Db
HTML/PDF/EXCEL/HTML Active
 
Posts: 90 | Registered: November 03, 2009Reply With QuoteReport This Post
Expert
posted Hide Post
SUM
'CNT.DST.J0.SALES_CURR_PREV_FY_US.ORDER_NUM'
BY 'SHIP2LIST.SEG01.DIVISION' AS 'DIVISION'
SUM
'CNT.DST.J0.SALES_CURR_PREV_FY_US.ORDER_NUM' WITHIN TABLE
UNITS_PURCH AS 'U_PURCHASED'
'J0.SALES_CURR_PREV_FY_US.PURCH_DOLLARS' AS 'P_DOLLARS'
UNITS_RETURNED AS 'U_RETURNED'
'J0.SALES_CURR_PREV_FY_US.RETURN_DOLLARS' AS 'R_DOLLARS'
'J0.SALES_CURR_PREV_FY_US.GP_DOLLARS' AS 'GP_DOLLARS'
BY 'SHIP2LIST.SEG01.DIVISION' AS 'DIVISION'
BY 'J0.SALES_CURR_PREV_FY_US.GL_DT_FISCAL_YEAR' AS 'FYEAR'
BY 'J0.SALES_CURR_PREV_FY_US.GL_DT_FISCAL_QUARTER' AS 'FQTR'
BY 'J0.SALES_CURR_PREV_FY_US.GL_DT_FISCAL_PERIOD' AS 'FPER'
BY 'J0.SALES_CURR_PREV_FY_US.GL_YR_MONTH' AS 'YR_MO'
BY 'J0.SALES_CURR_PREV_FY_US.ORDER_METHOD' AS 'ORD_METH'
BY 'SHIP2LIST.SEG01.REGIONAL_MGR_ID' aS 'REG_MGR'
BY 'J0.SALES_CURR_PREV_FY_US.SALES_MGR_ID' AS 'SALES_MGR'
BY 'J0.SALES_CURR_PREV_FY_US.PR_BR' AS 'BRANCH'
BY 'SHIP2LIST.SEG01.BRANCH_ABBREV' AS 'BRANCH_NAME'
BY 'J0.SALES_CURR_PREV_FY_US.ORDER_NUM'


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1960 | Location: Centennial, CO | Registered: January 31, 2006Reply With QuoteReport This Post
Gold member
posted Hide Post
Hi Tom!

I wondered if a multiverb request would be the way to go.

I tried your code and here's the error I gotFrownerFOC1856) ONLY ONE DISTINCT FIELD IS ALLOWED IN AGGREGATION


WebFOCUS 7.7.03
Linux / Universe Db
HTML/PDF/EXCEL/HTML Active
 
Posts: 90 | Registered: November 03, 2009Reply With QuoteReport This Post
Expert
posted Hide Post
Bad Copy and Paste by Me!

Take out the duplicate under the second SUM:

SUM
UNITS_PURCH AS 'U_PURCHASED'
'J0.SALES_CURR_PREV_FY_US.PURCH_DOLLARS' AS 'P_DOLLARS'
UNITS_RETURNED AS 'U_RETURNED'
'J0.SALES_CURR_PREV_FY_US.RETURN_DOLLARS' AS 'R_DOLLARS'
'J0.SALES_CURR_PREV_FY_US.GP_DOLLARS' AS 'GP_DOLLARS'
BY 'SHIP2LIST.SEG01.DIVISION' AS 'DIVISION'
BY 'J0.SALES_CURR_PREV_FY_US.GL_DT_FISCAL_YEAR' AS 'FYEAR'
BY 'J0.SALES_CURR_PREV_FY_US.GL_DT_FISCAL_QUARTER' AS 'FQTR'
BY 'J0.SALES_CURR_PREV_FY_US.GL_DT_FISCAL_PERIOD' AS 'FPER'
BY 'J0.SALES_CURR_PREV_FY_US.GL_YR_MONTH' AS 'YR_MO'
BY 'J0.SALES_CURR_PREV_FY_US.ORDER_METHOD' AS 'ORD_METH'
BY 'SHIP2LIST.SEG01.REGIONAL_MGR_ID' aS 'REG_MGR'
BY 'J0.SALES_CURR_PREV_FY_US.SALES_MGR_ID' AS 'SALES_MGR'
BY 'J0.SALES_CURR_PREV_FY_US.PR_BR' AS 'BRANCH'
BY 'SHIP2LIST.SEG01.BRANCH_ABBREV' AS 'BRANCH_NAME'
BY 'J0.SALES_CURR_PREV_FY_US.ORDER_NUM'


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1960 | Location: Centennial, CO | Registered: January 31, 2006Reply With QuoteReport This Post
Gold member
posted Hide Post
It still doesn't like it.

(FOC1867) DST OPERATOR MUST BE AT THE LOWEST LEVEL OF AGGREGATION


So, I'm assuming that this means it wants it within the second SUM? If so, then what would I put in the first SUM? The other fields I am aggregating? I'll try that..


WebFOCUS 7.7.03
Linux / Universe Db
HTML/PDF/EXCEL/HTML Active
 
Posts: 90 | Registered: November 03, 2009Reply With QuoteReport This Post
Gold member
posted Hide Post
Hmm - no dice on that.

How about an actual counter? Would that be something to try? I have to believe there is a way to do this using the functions, though.

Thank you!


WebFOCUS 7.7.03
Linux / Universe Db
HTML/PDF/EXCEL/HTML Active
 
Posts: 90 | Registered: November 03, 2009Reply With QuoteReport This Post
Expert
posted Hide Post
Jeralee,

Again, I don't go through everyone's code "exactly".

Take out your last sort:

BY 'J0.SALES_CURR_PREV_FY_US.ORDER_NUM'

Actually, try this:

 
TABLE FILE SHIP2LIST
SUM 
     'CNT.DST.J0.SALES_CURR_PREV_FY_US.ORDER_NUM'
     UNITS_PURCH AS 'U_PURCHASED'
     'J0.SALES_CURR_PREV_FY_US.PURCH_DOLLARS' AS 'P_DOLLARS'
     UNITS_RETURNED AS 'U_RETURNED'
     'J0.SALES_CURR_PREV_FY_US.RETURN_DOLLARS' AS 'R_DOLLARS'
     'J0.SALES_CURR_PREV_FY_US.GP_DOLLARS' AS 'GP_DOLLARS'

BY 'J0.SALES_CURR_PREV_FY_US.GL_DT_FISCAL_YEAR' AS 'FYEAR'
BY 'J0.SALES_CURR_PREV_FY_US.GL_DT_FISCAL_QUARTER' AS 'FQTR'
BY 'J0.SALES_CURR_PREV_FY_US.GL_DT_FISCAL_PERIOD' AS 'FPER'
BY 'J0.SALES_CURR_PREV_FY_US.GL_YR_MONTH' AS 'YR_MO'
BY 'J0.SALES_CURR_PREV_FY_US.ORDER_METHOD' AS 'ORD_METH'
BY 'SHIP2LIST.SEG01.REGIONAL_MGR_ID' aS 'REG_MGR'
BY 'J0.SALES_CURR_PREV_FY_US.SALES_MGR_ID' AS 'SALES_MGR'
BY 'J0.SALES_CURR_PREV_FY_US.PR_BR' AS 'BRANCH'
BY 'SHIP2LIST.SEG01.BRANCH_ABBREV' AS 'BRANCH_NAME


Tom


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1960 | Location: Centennial, CO | Registered: January 31, 2006Reply With QuoteReport This Post
Gold member
posted Hide Post
That's almost exactly where I started, but wihtout the WITHIN. I'm getting results but its still giving me a 1 on every line, even though they are on the same order.

As always - thank you for your patience with me!


WebFOCUS 7.7.03
Linux / Universe Db
HTML/PDF/EXCEL/HTML Active
 
Posts: 90 | Registered: November 03, 2009Reply With QuoteReport This Post
Expert
posted Hide Post
Then try the 'CNT.DST.J0.SALES_CURR_PREV_FY_US.ORDER_NUM' WITHIN DIVISION not TABLE...


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1960 | Location: Centennial, CO | Registered: January 31, 2006Reply With QuoteReport This Post
Gold member
posted Hide Post
That gives me the total number of orders in the result set. I'm looking for a result like this.

Order Product CNT.DST.Order
1 TIRES 1
1 TUBES 0
1 WHEELS 0
2 TIRES 1
3 TUBES 1


That way I can SUM the CNT.DST.Order column and get a total number of orders in that set...

Am I totally goofing this all up??


WebFOCUS 7.7.03
Linux / Universe Db
HTML/PDF/EXCEL/HTML Active
 
Posts: 90 | Registered: November 03, 2009Reply With QuoteReport This Post
Gold member
posted Hide Post
Hi everyone,

Any other suggestions on how I can accomplish this? Am I missing something really simple?

Thanks!
Jeralee


WebFOCUS 7.7.03
Linux / Universe Db
HTML/PDF/EXCEL/HTML Active
 
Posts: 90 | Registered: November 03, 2009Reply With QuoteReport This Post
Virtuoso
posted Hide Post
I am confused by your request. I would assume order number to be be unique for every order. Therefore a count of distinct order numbers from the TABLE would in fact be a count of the number of records in the table. No?

For example, in this query against the GGSALES sample table, field SEQ_NO is unique for every record. So the count of distinct SEQ_NO WITHIN TABLE will always be the same number, no matter which BY fields are used.

TABLE FILE GGSALES
 SUM 
  CNT.DST.SEQ_NO WITHIN TABLE
  UNITS
  DOLLARS
 BY REGION
 BY ST
 BY CATEGORY
 BY PCD
 BY PRODUCT
END


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Reply With QuoteReport This Post
Gold member
posted Hide Post
Hi Dan,

Actually there are multiple records per order because the data stores each line number separately. Therefore, I need to count each order number only once.

I thought this would be something easy to get but it's turning out to be more confusing than I expected.

Thank you!


WebFOCUS 7.7.03
Linux / Universe Db
HTML/PDF/EXCEL/HTML Active
 
Posts: 90 | Registered: November 03, 2009Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Based on your sample output above, it appears you want the distinct count to appear for only the first record in a "set". You should use that "set" value to define your WITHIN clause for the CNT.DST. operator. Then you can use a COMPUTE to limit the resulting counts to the first record in each set. For example:

TABLE FILE GGSALES
 SUM
  CNT.DST.PCD/I5 WITHIN REGION
  COMPUTE CNT_DST_PCD/I5C = IF (REGION NE LAST REGION) THEN CNT.DST.PCD ELSE 0 ;
  UNITS
  DOLLARS
 BY REGION
 BY ST
 BY CATEGORY
 BY PRODUCT
END


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Not able to get a distinct count on a field

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