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     [CLOSED] Total for distinct values based on a different value

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[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, 2017Report 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: 146 | Registered: November 09, 2015Report 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, 2013Report 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: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report 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 8206M, iWay DataMigrator, Windows, DB2 Windows V10.5, MS SQL Server, Azure SQL, Hyperstage, ReportCaster
 
Posts: 168 | Registered: March 29, 2013Report 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.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report 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, 2017Report 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: 2128 | Location: Customer Support | Registered: April 12, 2005Report 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     [CLOSED] Total for distinct values based on a different value

Copyright © 1996-2020 Information Builders