Accumulate into a Table

K

Kevin Williams

New Member
I have searched the forum for a possible answer to the below listed problem and could not find a solution that combines the action of a Accumulate command with the insertion into a table (temp or otherwise)

Basically I am needing an accumulate command that will usually display as

BY
Part, Instance, Total
aaa, 1, 45
aaa, 2, 3
aaa, 4, 224
aaa, 5, 35

I want to store the total into a table with a field based on the number.

The table structure would have an array type field structure for the total. The Accumulate BY instance would be the choice for the array slot.

Table
Part, Instance[1], Instance[2], Instance[3], Instance[4], Instance[5]
aaa, 45, 3, , 224, 35

I am new to progress programming but I will be parsing a very large table and did not want to so the summation manually whe progress is so efficient at this type of record related math.

Any experience or suggestions for alternate methods would be greatly appreciated. Otherwise I am stuck with if then else statements.

Regards,
Kevin Williams
 
I don't understand what you want to do. But if you could give an example using the variables and IF ... THEN logic it might be clearer.

BTW -- to get stuff to align use [ C O D E ] tags around the relevant sections. Like this:

Code: 
col1   col2
  12     45
  67     78
 
You probably want to read up on BREAK BY. Combining your ACCUMULATE and and an appropriate LAST-OF will give you a place to create the table and post the current ACCUM value.
 
As I proceed with an accumulate command I will be creating accumulated values of a qty field. The major BY of the accumulated field will be month number.

The table being parsed will be like

Code: 
part    month    qty
aaa        1        45
aaa        1        14
aaa        2         5
bbb        1         7
ccc        1         4
bbb        1         3
aaa        3        65
When I run my Accumulate with break on month it works correctly and I get the following output:

Code: 
part    month    qty
aaa        1        59      <-- this line of output, how can I assign it to fields
aaa        2         5             in my database table
aaa        3        65
bbb        1        10
ccc        1         4
The output table needs to be populated like this:
Code: 
part       month[1]   month[2]    month[3]    month[4]
aaa          59          5           65            
bbb          10   
ccc           4
The issue that I have is that while I can process the BREAK BY correctly I can't find a way to take the accumulated values and store them to their grouped month field one record at a time.

Even if I only select aaa records to accumulate I still end up with an output of multiple lines that I need to then store in one record of multiple fields.

I am currently working on a piece of code with if then looping but I did not want to subject my test server to parsing 70 million records 12 times for each part in each month.

I hope that this can provide some clarification....

Regards,
Kevin
 
I think you want LAST-OF, something like this:

Code: 
FOR EACH record BREAK BY month:
    totalqty = totalqty + monthqty.
    IF LAST-OF (month) THEN DO:
        someotherrecord.totalqty = totalqty.
        totalyqty = 0.
    END.
END.
Though something like this should work, I maintain a healthy distrust of BREAK BY. It's one of those things that works OK in simple cases (when it would be easy enough to do it 'the old fashioned way') but can easily lead you astray, and you end up going back to 'the old fashioned way' anyway.

For example: if you put an 'IF... NEXT' between the FOR EACH and the LAST-OF, you will probably have problems.
 
You seem to have two separate meanings for the "month" field. One seems to indicate "month number" in the usual sense. The other seems to be an array that you would like to put the accumulated count for the month indexed by the month number into.

If that is correct then you need 2 distinct fields. monthNum and monthCount[].

Of course from a relational design standpoint this is a really, really bad design... So I hope my interpretation of your desire is wrong. But if it is correct what you really ought to have is a different table to hold the accumulated values in. That's still "unclean" since those values are "derived values" but it would be a lesser evil (and a fairly common de-normalization).

Totally untested psuedo-code:

Code: 
define temp-table partCount no-undo
  field partID as character
  field monthNum as integer
  field monthCount as integer
.

for each part no-lock break by part by month:
  if last-of( month ) then
    do:
      create partCount.
      assign
        partCount.partID = part.part
        partCount.monthNum = part.month
        partCount.monthCount = accum( whatever )
      .
    end.
end.

for each partCount no-lock:
  display partCount.
end.
 
You must log in or register to reply here.
Back
Top