Solved

# What's an easy way to add cumulatively in Foxpro?

Posted on 2009-12-28
303 Views
I have a cursor whose data is in the following format:

Date Class Value
1/1/2000  RED    1.5
1/2/2000  RED    1.3
1/3/2000  RED    1.1
1/4/2000  RED    1.7
1/1/2000  BLUE    0.65
1/2/2000  BLUE    0.75
1/3/2000  BLUE    0.85
1/4/2000  BLUE    0.90
1/1/2000  GREEN    21.5
1/2/2000  GREEN    22.5
1/3/2000  GREEN    24.5
1/4/2000  GREEN    23.5

I am trying to add a cumulative value column, which is a sum of the value column, but added from most recent day to past, but done by class, as the following example indicates:

Date      Class   Value   Cum_Value
1/1/2000  RED    1.5       5.6
1/2/2000  RED    1.3       4.1
1/3/2000  RED    1.1       2.8
1/4/2000  RED    1.7       1.7
1/1/2000  BLUE    0.65   3.15
1/2/2000  BLUE    0.75   2.50
1/3/2000  BLUE    0.85   1.75
1/4/2000  BLUE    0.90   0.90
1/1/2000  GREEN    21.5    92.0
1/2/2000  GREEN    22.5    70.5
1/3/2000  GREEN    24.5    48.0
1/4/2000  GREEN    23.5    23.5

What's the easiest and quickest (computationally) way of doing this?
0
Question by:jglapski

LVL 41

Accepted Solution

Several ways exist but the classic xBase code seems to be the most efficient one:
*-- Suppose the numeric column Cum_Value already exists in YourTable

USE YourTable EXCLUSIVE

INDEX ON Class + DTOC(Date, 1) TAG CompOrder DESCENDING

LOCAL lnCumulSum, lcClass

lcClass = ''

lnCumulSum = 0

SCAN ALL

IF Class == lcClass

lnCumulSum = lnCumulSum + Value

ELSE

lnCumulSum = Value

lcClass = Class

ENDIF

REPLACE Cum_Value WITH lnCumulSum

ENDSCAN

DELETE TAG CompOrder

GO TOP

0

LVL 41

Expert Comment

If you have just read-only cursor without the Cum_value column you may create new one by

SELECT *, 000000.000 As Cum_Value
FROM YourCursor

and work with this new cursor.
0

## Featured Post

### Suggested Solutions

scoresIncreasing challenge 10 48
Excel Hangs / Not enough Memory 5 47
WMI, model #, retrieving information 10 57
python question 5 38
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
If you’re thinking to yourself “That description sounds a lot like two people doing the work that one could accomplish,” you’re not alone.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …