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,

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?

You can create 2 hold file-
1- unique total based on Num3.
2- Detail report.

Marge both file and get the data.

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.

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.

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

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.

Rob

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

Thanks