Results 1 to 6 of 6
  1. #1
    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 )

  2. #2
    Join Date
    Sep 2009
    Location
    Ontario
    Posts
    1,004
    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.

  3. #3
    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

  4. #4
    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

  5. #5
    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

  6. #6
    Join Date
    Nov 2012
    Posts
    24
    Hi all
    It works as expected. Many thanks. Slowly FoxPro is becoming clearer.

    MANY THANKS!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •