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] Total for distinct values based on a different value
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] Total for distinct values based on a different value
 Login/Join
 
Member
posted
Hello,

I'm new to WebFocus but making good headway on learning the system. I have a question about getting a sum. Here is what I'm looking for

Num1 Num2 Num3Name1 Name2

1 10 10 Jane Doe
2 3 11 Jack Doe
3 3 11 Jack Doe
4 7 12 John Doe

Total for Num2 20

I need to total up Num2 based on the distinct value on Num3 while keeping the data on the page to show. This will cause my NUM2 to have a different end result then if you were to add up all of NUM2. Does my description make any sense? Apologies up front if its confusing.

This message has been edited. Last edited by: FP Mod Chuck,


WebFOCUS 7.7.03,
Webfocus 8.2.03,
Windows,
All Outputs
 
Posts: 12 | Location: Florida | Registered: January 05, 2017Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Hello,

Are Num2 and Num3 always going to be the same upon distinct Num3? What happens if you have the following minor change in your data values:

Num1 Num2 Num3Name1 Name2
1 10 10 Jane Doe
2 3 11 Jack Doe
3 6 11 Jack Doe
4 7 12 John Doe

Does Num2 still equal 20 or does it now equal 23? How would you know to pick row 2 or row 3 since Num3 in both match distinctly?


JC
WebFOCUS Dev Studio / App Studio
8.2.01
Windows 7
 
Posts: 144 | Registered: November 09, 2015Reply With QuoteReport This Post
Platinum Member
posted Hide Post
You can create 2 hold file-
1- unique total based on Num3.
2- Detail report.

Marge both file and get the data.


Thanks!
@vi

WebFOCUS 8105, Dev Studio 8105, Windows 7, ALL Outputs
 
Posts: 103 | Registered: July 08, 2013Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Rob,
First of all, welcome to the WebFOCUS forum!
About your question, with the basic assumption that when NUM3 repeats itself, so does NUM2, here is what I would do:
  
-* File robbrown01.fex
-* Create an in-memory master for ROB
EX -LINES 7 EDAPUT MASTER,ROB,C,MEM
FILENAME=ROB, SUFFIX=FIX
SEGNAME=ROB, SEGTYPE=S0
FIELDNAME=NUM1, ALIAS=NUM1, FORMAT=I2, ACTUAL=A1,$
FIELDNAME=NUM2, ALIAS=NUM2, FORMAT=I3, ACTUAL=A3,$
FIELDNAME=NUM3, ALIAS=NUM3, FORMAT=I3, ACTUAL=A3,$
FIELDNAME=NAME, ALIAS=NAME, FORMAT=A9, ACTUAL=A9,$
-RUN
-* Logical data file ROB
FILEDEF ROB DISK ROB.FTM
-RUN
-* Put data into physical file ROB
-WRITE ROB 1 10 10 Jane Doe
-WRITE ROB 2  3 11 Jack Doe
-WRITE ROB 3  3 11 Jack Doe
-WRITE ROB 4  7 12 John Doe
-RUN
-* Obtain desired outptu
TABLE FILE ROB
SUM FST.NUM2
BY NUM3
ON TABLE SUMMARIZE
END

If the assumption is wrong, come back.


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1932 | Location: Tel Aviv, Israel | Registered: March 23, 2006Reply With QuoteReport This Post
Platinum Member
posted Hide Post
I do not think there needs to be a dependency on NUM3 with same behaviour. All that is needed for the hold file is a BY on NUM2 and a total. You can put whatever in the SUM with a NOPRINT.

TABLE FILE ROB
SUM CNT.NAME NOPRINT
BY NUM2
ON TABLE SUMMARIZE
END


But as indicated by the others, you do need hold files, I do not think you can do this in one table request.

Martin.


WebFocus 8203M, iWay DataMigrator, Windows, DB2 Windows V10.5, MS SQL Server, Azure SQL, Hyperstage, ReportCaster
 
Posts: 153 | Registered: March 29, 2013Reply With QuoteReport This Post
Expert
posted Hide Post
Hi Rob and welcome to the FP Forum,

Your question is clear (as the number of responses attest!) but JC raises a very pertinent point. If the NUM2 value changes, which one do you need to sub-total?

If the answer is that the value remains the same, then you do not need to double handle the data using a MATCH process, you can achieve this in one pass -

-* Using Danny's prepared data :)
TABLE FILE ROB
PRINT COMPUTE DST_NUM2/I3 MISSING ON = IF NUM2 EQ LAST NUM2 THEN MISSING ELSE NUM2; NOPRINT
      NUM1
      NUM2
      NUM3
      NAME
   BY NUM1 NOPRINT
   BY NUM2 NOPRINT
   BY NUM3 NOPRINT
ON TABLE SUBFOOT
"Total for Num2 = <ST.DST_NUM2"
ON TABLE SET PAGE NOLEAD
END


Good luck

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.06 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5601 | Location: United Kingdom | Registered: April 08, 2004Reply With QuoteReport This Post
Member
posted Hide Post
Hello everyone.

jcannavo

The Num2 and Num3 will always be the same for distinct Num3. So the value would stay 20.

Avinash

I'm not to the point in my knowledge to know how to accomplish your solution just yet.

Danny-SRL

Your solution looks like it may be what I'm looking to do.

Martin vK

If I read your suggestion right I may be able to get the end result im looking for as long as there is no exact match needed for the Name column. Human error can play a role here.

Tony A

Once Value Num2 and Num3 are in the database then they do not change. So your may also be a solution at this point.


Thank you everyone for your responses. It's great to see a forum with such active and knowledgeable participants. I'm going to dig in with some of these replies and let you all know how it turned out. Thank you so much again.


WebFOCUS 7.7.03,
Webfocus 8.2.03,
Windows,
All Outputs
 
Posts: 12 | Location: Florida | Registered: January 05, 2017Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Rob

Have you had a chance to test this out? If so what were the results.

Thanks


Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
 
Posts: 1566 | 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] Total for distinct values based on a different value

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