Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2012
    Posts
    24

    Unanswered: 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,057
    Provided Answers: 1
    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
    122
    Provided Answers: 1
    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
    122
    Provided Answers: 1
    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
    122
    Provided Answers: 1
    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
  •