?
Solved

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

Posted on 2009-12-28
2
Medium Priority
?
394 Views
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?
0
Comment
Question by:jglapski
  • 2
2 Comments
 
LVL 43

Accepted Solution

by:
pcelba earned 2000 total points
ID: 26135068
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

Open in new window

0
 
LVL 43

Expert Comment

by:pcelba
ID: 26135088
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
  INTO CURSOR YourRWCursor READWRITE

and work with this new cursor.
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Today, the web development industry is booming, and many people consider it to be their vocation. The question you may be asking yourself is – how do I become a web developer?
The SignAloud Glove is capable of translating American Sign Language signs into text and audio.
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 …
Loops Section Overview

831 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