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

Posted on 2009-12-28
Last Modified: 2013-11-24
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?
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
    INDEX ON Class + DTOC(Date, 1) TAG CompOrder DESCENDING
    LOCAL lnCumulSum, lcClass
    lcClass = ''
    lnCumulSum = 0
      IF Class == lcClass
        lnCumulSum = lnCumulSum + Value
        lnCumulSum = Value
        lcClass = Class
      REPLACE Cum_Value WITH lnCumulSum
    DELETE TAG CompOrder
    GO TOP

    Open in new window

    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.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    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 …

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now