Shifting Data 1 Observation down.

[#1]

Hi I'm wondering how can I shift my data 1 observation down. E.g.

Name          NewName

John        

Lennon         John

Kenny          Kenny

Bob              Bob

Zack            Zack

Let me give a case scenario. For example I want to look at the sales generated by my e-commerce websites based hourly in 4 days.

Website A generates around $50 of sales hourly but occasionally it can generate $20 or even $100. I want to look at the difference of the sales over the Hours and Days and how different are each sales from the previous hour. Also, the expected sale is at least $30. so I'll be using e.g. $50/$30 to see how well is each website doing so an example on pivot chart would be:

Day       WebsiteID        Hour          Sales          Difference from previous hour(+-)

12           011                  1               50               0

                                      2               20               30

                                     3               39               19

                                    23              90               0

13           011                 1               90               0

                                    2               80              10

                                    3               40               40

14           022                 1               50                0

                                    2               30               20

                                    3               30               0

15           022                 1               230              0

                                    2              180               50

                                    3               86                94

May I know what is the best way to do this. Is creating a new field with all the Sales shifted 1 observation down a good way? Thanks in advance

[hic]

You can do this with the Peek() function in the script, e.g.:

Load

     Day,

     Hour,

     Sales,

     Sales - Peek(Sales) as Difference

     Resident Table

     Order By Day, Hour ;

HIC

[its_anandrjs · Accepted solution]

You can achieve this by the use of Peek() and Previous() functions see the load scripts

//By the use of the Previous function

Raw:

LOAD * INLINE [

    Day, Hour, Sales

    12, 1, 50

    12, 2, 20

    12, 3, 39

    12, 23, 90

    13, 1, 90

    13, 2, 80

    13, 3, 40

    14, 1, 50

    14, 2, 30

    14, 3, 30

    15, 1, 230

    15, 2, 180

    15, 3, 86

];

Final:

LOAD *,if(Day <> Previous(Day),0, fabs(Sales - Previous(Sales))) as Difference

Resident Raw Order by Day, Hour;

DROP Table Raw;

//By the use of peek functions and use raw file

Final:

LOAD *,if(Day <> Peek(Day),0, fabs(Sales - Peek(Sales))) as Difference

Resident Raw Order by Day, Hour;

DROP Table Raw;

Note:- Fabs is used here to remove - sign if you require this dont put this function.

[#4]

Oops I forgot to include that there is another field called Website ID in between Day and Hour!