Peek till not null

    I see this question getting repeated. So, thought of posting it here.


    ID NumberStationStart StationEnd StationJourney


    Station 1

    Station 1-
    102Station 2


    103Station 3--
    104Station 4--
    105Station 5--
    106Station 6--
    107Station 7-Station 7


    Station 6Station 6-
    105Station 5--
    104Station 4-Station 4


    The Start Station should be filled with the Previous Station when it is blank and if the previous station as well is blank, it should take one more step back.


    //Script Start


    //Loading the data


    LOAD [ID Number],


         [Start Station],

         [End Station],




    (ooxml, embedded labels, table is Sheet1);


    //note the difference between [Start Station] & StartStation

    //StartStation is the new field which we are going to populate




    if(IsNull([Start Station]),Peek([StartStation]),[Start Station]) as StartStation

    Resident Test;


    //Script end


    Here, StartStation is the new field that is getting created with the original value for the first time. Say Station 1.


    And from here, whenever [Start Station] has a null value, it'll get peek(StartStation), the new field which has value "Station 1" and will be stored in the same new field StartStation.

    And it doesn't matter how many rows have null value, it should go only one step back.


    After this load statement is executed, StartStation will have all the rows filled with the previous value.


    //Script start


    DROP Table Test;

    RENAME Table Test1 to Test;


    //Script end


    Drop the first table and rename the latest table as Test.


    I have answered this here


    I have enclosed the source data and the qvw here.