Peek till not null

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

     

    ID NumberStationStart StationEnd StationJourney

    101

    Station 1

    Station 1-
    102Station 2

    -

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

    106

    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

    Test:

    LOAD [ID Number],

         Station,

         [Start Station],

         [End Station],

         Journey

    FROM

    peektillnull.xlsx

    (ooxml, embedded labels, table is Sheet1);

     

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

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

    Test1:

    LOAD

    *,

    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 https://community.qlik.com/message/434207#434207

     

    I have enclosed the source data and the qvw here.

     

     

    Cheers,

    Naresh