Rexx cumulative values

[Per E. Johannessen]

In a VX-Rexx program I have this container, QRY_2:

|TEKSTFELT | TALLFLT1 | TALLFLT2 |
|aaa       |     10.00|          |
|aaa       |     10.00|          |
|aaa       |     10.00|          |
|bbb       |    100.00|          |
|bbb       |    100.00|          |
|bbb       |    100.00|          |
|ccc       |   1000.00|          |
|ccc       |   1000.00|          |
|ccc       |   1000.00|          |

Running this code (from a push button):

ok = VRMethod( "QRY_2", "MoveFirst" )
do while( ok = 1 )
    call VRMethod "QRY_2", "RowData", "data."
    str = ""
    do i = 1 to data.0
        str = str data.i
    end
    say str
    ok = VRMethod( "QRY_2", "MoveNext" )
end

Results in:

aaa 10.00 DATA.3
aaa 10.00 DATA.3
aaa 10.00 DATA.3
bbb 100.00 DATA.3
bbb 100.00 DATA.3
bbb 100.00 DATA.3
ccc 1000.00 DATA.3
ccc 1000.00 DATA.3
ccc 1000.00 DATA.3

The problem is that I need the third column in the container, TALLFLT2 (displayed as DATA.3 above), to get updated with cumulative values from column TALLFLT1, also it must «reset» when the value in TEKSTFELT changes, like this:

|TEKSTFELT | TALLFLT1 | TALLFLT2|
|aaa       |     10.00|    10.00|
|aaa       |     10.00|    20.00|
|aaa       |     10.00|    30.00|
|bbb       |    100.00|   100.00|
|bbb       |    100.00|   200.00|
|bbb       |    100.00|   300.00|
|ccc       |   1000.00|  1000.00|
|ccc       |   1000.00|  2000.00|
|ccc       |   1000.00|  3000.00|

Ideas are welcome.

[Mentore]

In a VX-Rexx program I have this container, QRY_2:

The problem is that I need the third column in the container, TALLFLT2 (displayed as DATA.3 above), to get updated with cumulative values from column TALLFLT1, also it must «reset» when the value in TEKSTFELT changes, like this:

|TEKSTFELT | TALLFLT1 | TALLFLT2|
|aaa       |     10.00|    10.00|
|aaa       |     10.00|    20.00|
|aaa       |     10.00|    30.00|
|bbb       |    100.00|   100.00|
|bbb       |    100.00|   200.00|
|bbb       |    100.00|   300.00|
|ccc       |   1000.00|  1000.00|
|ccc       |   1000.00|  2000.00|
|ccc       |   1000.00|  3000.00|

Ideas are welcome.

Apart from REXX  and VX-Rexx, of which I remember too little, the idea is keeping track of all the equal elements in DATA.1 and increment the cells in DATA.3 by the amount of what is present on the corresponding cell in DATA.2 (convoluted, I know: I'm terribly sleepy today).

One idea would be to follow a SQL-like approach, selecting all distinct elements in DATA.1 and for each cell in DATA.3 sum the actual amount of DATA.2.
Seeing as the rows in DATA.1 are already grouped by equal elements that would be not so difficult.

Another idea, more trivial but clever:
1) keep a counter of the current cell
2) save the current value of DATA.1 in an object, and the current value of DATA.3 in another (current cells)
3) cycle on all elements of DATA.1:
3.1) while DATA.1 is equal to the saved value, add to the current cell of DATA.3 the previous value + DATA.2
3.2) if DATA.1 is different, repeat the cycle saving the new value and starting from empty values of DATA.2 and DATA.3

Clear as fog, I know...

Mentore

[Per E. Johannessen]

Thanks Mentore, I have already tried with SQL and I think that's the best way. I've been trying various SQL statements that I have found on the net with DBExpert but so far no success. DBE does not come with any documentation of valid SQL-statements and I haven't found any online manual either. However, even if I could make it work with DBE I probably will have trouble making it work with VX-Rexx.

[Alex Taylor]

Try something like:

Code: [Select]

ok = VRMethod( "QRY_2", "MoveFirst" )
row = 0
do while( ok = 1 )
    call VRMethod "QRY_2", "RowData", "data."
    prev = row
    row = row + 1
    if row == 1 then
        data.row.3 = data.row.2
    else
        data.row.3 = data.prev.3 + data.row.2
    say data.row.1 ',' data.row.2 ',' data.row.3
    ok = VRMethod( "QRY_2", "MoveNext" )
end

[Lars]

Assuming strict ordering of field TEKSTFELT:

Code: [Select]

ok = VRMethod( "QRY_2", "MoveFirst" )
identifier = ''
sum = 0
do while( ok = 1 )
    call VRMethod "QRY_2", "RowData", "data."
    if data.1 <> identifier then do
       identifier = data.1
       sum = data.2
   end
   else do
       sum = sum + data.2
   end
   data.3 = sum
   say data.1 ',' data.2 ',' data.3
   ok = VRMethod( "QRY_2", "MoveNext" )
end


[Per E. Johannessen]

Thank you both.

Alex, I got this when running your code:
 *** Error 41 Bad aritmetic conversion

Lars, works fine.

Initially I had the idea that the container should display these cumulative values but there is actually no point in that.
Will use the code to create some accounting reports which also can be printed to screen.

[Per E. Johannessen]

Tried to create a report, which works but it is missing the cumulative values.
I added the following to Lars' code:

    reprows = 0
ok = VRMethod( "QRY_2", "MoveFirst" )
do while( ok = 1 )
    call VRMethod "QRY_2", "RowData", "data."
    reprows=reprows+1
    table.reprows.TEKSTFELT = data.1
    table.reprows.TALLFLT1 = data.2
    table.reprows.TALLFLT2 = data.3

ok = VRMethod( "QRY_2", "MoveNext" )
end

table.0=reprows

DO i=1 TO table.0

END i

hwnd = VRGet( "Window1", "HWnd" )

rc=VpPrintReport(hwnd, 'REP_HBAKKM.vpt', 'table.', '<PREVIEW>' )

call QRY_2_Fini

return

Attached is the resulting report which would be fine if the column now showing data.3 would show the cumulative instead.

[Per E. Johannessen]

Well, there is only data from "QRY_2" that is being used in the report.

Writing to a txt-file is OK, like this;
        outfile = data.1 ',' data.2 ',' data.3
        out = lineout(testakkm.txt,outfile)

So, how to define the same data as source for the VPR-report REP_HBAKKM.vpt ?

[Per E. Johannessen]

In case someone is interested, this code works:

   reprows = 0

ok = VRMethod( "QRY_2", "MoveFirst" )
identifier = ''
sum = 0
do while( ok = 1 )
   call VRMethod "QRY_2", "RowData", "data."
   if data.1 <> identifier then do
   identifier = data.1
   sum = data.2

   end
   else do
   sum = sum + data.2
   end
   data.3 = sum

   reprows=reprows+1
   data.reprows.TEKSTFLT = data.1
   data.reprows.TALLFLT1 = data.2
   data.reprows.AKKUM = data.3

ok = VRMethod( "QRY_2", "MoveNext" )
end

data.0=reprows

DO i=1 TO data.0

END i

hwnd = VRGet( "Window1", "HWnd" )

rc=VpPrintReport(hwnd, 'REP_HBAKKM.vpt', 'data.', '<PREVIEW>' )