 |
|||||
|
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
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 WebFOCUS/FOCUS Forum on Focal Point [CLOSED] Total for distinct values based on a different value
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [CLOSED] Total for distinct values based on a different value Read-Only TopicGo | Search | Notify | Tools |
| [CLOSED] Total for distinct values based on a different value | Login/Join |
| Member |
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 | ||
|
| Platinum Member |
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 | |||
|
| Platinum Member |
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 | |||
|
| Virtuoso |
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 | |||
|
| Platinum Member |
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 8206M, iWay DataMigrator, Windows, DB2 Windows V10.5, MS SQL Server, Azure SQL, Hyperstage, ReportCaster | |||
|
| Expert |
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
ENDGood luck T
| |||||||||
|
| Member |
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 | |||
|
| Virtuoso |
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 | |||
|
| Powered by Social Strata |
 | Please Wait. Your request is being processed... |
Read-Only TopicFocal Point Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [CLOSED] Total for distinct values based on a different value
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [CLOSED] Total for distinct values based on a different valueCopyright © 1996-2020 Information Builders
| View $GS_USERNAME's Public Profile |
| Add $GS_USERNAME to my Ignore List |
| View Recent Posts by $GS_USERNAME |
| Notify me of New Posts by $GS_USERNAME |