Shift rows in FoxPro

Orongo123 · #1 (**permalink**) 12-04-12, 04:25

Hi

I have data that looks something like this

HTML Code:

year	value
-------------
2009	  1
2010	  2
2011	  3
2012	  4

And I want to shift one column according

HTML Code:

year	value
-------------
2009	  2
2010	  3
2011	  4
2012

Also I want it to shift downwards

HTML Code:

year	value
-------------
2009	  
2010	  1
2011	  2
2012	  3

So far I am trying to use the year as a condition for shifting, but it doesn't work. Nothing happens to the table

Code:

USE SourceData ALIAS ss
SELECT 	psres4pcgb  as Iorg,;
		psres4pcgb as I WHERE year = year -1;
FROM ss INTO TABLE bbb

CLOSE TABLES

Any ideas? I have scouted the internet for solutions to this. But have found nothing of use. (I am crossing my fingers here

)

kitaman · #2 (**permalink**) 12-04-12, 10:16

I have assumed that "value" is numeric, and that its value is zero where you have shown it as blank.

Code:

set echo off                         
set talk off                         
use SourceData                            
go bottom                            
prev_value=0                    
do while .not. bof()                 
        curr_value=value              
        replace value with prev_value 
        prev_value=curr_value          
        skip -1                      
enddo

Reads the table from the bottom to the top, and replaces value with the value from the previously read record.

Code:

set echo off                          
set talk off                          
use SourceData                             
prev_value=0                          
do while .not. eof()                  
        curr_value=value              
        replace value with prev_value 
        prev_value=curr_value         
        skip 1                        
enddo

Same program, but reading forward.
In both of these programs, the original data is modified.

jrbbldr · #3 (**permalink**) 12-04-12, 11:30

As with many other languages there are many ways to approach any single issue.

My approach to your questions might be something like the following which uses the ability of FP/VFP to set up RELATIONSHIPS between different tables

NOTE - A 'CURSOR' is just a data table that resides only in Memory (it is not a 'real' FILE). Otherwise is works the same.

Code:

use SourceData 
* --- Create Recipient Table/Cursor to Acquire New Values and not change original ---
SELECT *;
   FROM SourceData;
   INTO CURSOR NewData READWRITE

SELECT *;
  FROM SourceData;
  INTO CURSOR ShiftDown READWRITE

* --- Change Year in this temp table so that Values will be associated with Next Year ---
SELECT ShiftDown
REPLACE ALL Year WITH Year + 1
* --- Create Index on the Year Field value ---
INDEX ON Year TAG Year

* --- Establish a table Relation ---
SELECT NewData
SET RELATION TO Year INTO ShiftDown
* --- Remove Previous Value Field values ---
REPLACE ALL Value WITH 0
* --- Populate the Value Fields with ShiftDown Value Field values ---
REPLACE ALL Value WITH ShiftDown.Value

Similarly

Code:

use SourceData 
* --- Create Recipient Table/Cursor to Acquire New Values and not change original ---
SELECT *;
   FROM SourceData;
   INTO CURSOR NewData READWRITE

SELECT *;
  FROM SourceData;
  INTO CURSOR ShiftUp READWRITE

* --- Change Year in this temp table so that Values will be associated with Previous Year ---
SELECT ShiftUp
REPLACE ALL Year WITH Year - 1
* --- Create Index on the Year Field value ---
INDEX ON Year TAG Year

* --- Establish a table Relation ---
SELECT NewData
SET RELATION TO Year INTO ShiftUp
* --- Remove Previous Value Field values ---
REPLACE ALL Value WITH 0
* --- Populate the Value Fields with ShiftDown Value Field values ---
REPLACE ALL Value WITH ShiftUp.Value

At the end of the execution the new data will be in the table/cursor 'NewData'

Good Luck
JRB-Bldr

jrbbldr · #4 (**permalink**) 12-04-12, 11:50

One additional note - In the sample code I gave you I assumed that the Field 'YEAR' was an Integer field.

If it were to be a Character field then changes to the code would have to be made to take the Year shift 'up' and 'down' into account.

Good Luck

jrbbldr · #5 (**permalink**) 12-04-12, 12:15

An even SIMPLER approach....

Code:

cShift = "UP"   && Logical Memory Variable 'Flag' To Control Which Way to Shift Data

USE SourceData IN 0
SELECT *;
   FROM SourceData;
   INTO CURSOR NewResults READWRITE

SELECT NewResults
IF cShift = "UP"   
   * --- To Shift Up ---
   REPLACE ALL Year WITH Year + 1
ELSE
   * --- OR To Shift Down ---
   REPLACE ALL Year WITH Year - 1
ENDIF

New Shifted data in table/cursor 'NewResults' and original data retained in its original state for Archiving, etc.

Good Luck

Orongo123 · #6 (**permalink**) 12-05-12, 08:25

Hi all
It works as expected. Many thanks. Slowly FoxPro is becoming clearer.

MANY THANKS!

Internet Services

Web Development

Digital Marketing

Consumer Tech