If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > PC based Database Applications > Other PC Databases > Shift rows in FoxPro

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Nov 2012
Posts: 24
Shift rows in FoxPro

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 )
Reply With Quote
  #2 (permalink)  
Old
Programming since 1BC
 
Join Date: Sep 2009
Location: Ontario
Posts: 972
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.
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Nov 2002
Posts: 108
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
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Nov 2002
Posts: 108
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
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Nov 2002
Posts: 108
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
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Nov 2012
Posts: 24
Hi all
It works as expected. Many thanks. Slowly FoxPro is becoming clearer.

MANY THANKS!
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On