Filling values between start and end point in data table

    Recently i have come across a situation where I need to fill 'Y' in between two flags.. and 'N' after that.

     

    I found the solution using peek() function which was interesting and i thing it is good to share..

     

    First of let me explain the scenario.

     

    I had some product ID's where based on there ranks and some other business logic.. i had 'Y' as flag for first rank and in-between of series..

     

    My data looks like as below:

     

    Product IdRankFlag
    A1021Y
    A1022
    A1023
    A1024Y
    A1025
    A1026
    A1027
    A1031Y
    A1032
    A1033Y
    A1034
    A1035
    A1036

     

    So, I need to fill Y's between Y's of a ProductId and N else where.

     

    The required output is going to be

     

    Product IdRankFlag
    A1021Y
    A1022Y
    A1023Y
    A1024Y
    A1025N
    A1026N
    A1027N
    A1031Y
    A1032Y
    A1033Y
    A1034N
    A1035N
    A1036N

     

    ////////////////////////// Sample Code ///////////////////////////

     

    Source:

    LOAD * Inline

    [

    Product Id,Rank,Flag

    A102,1,Y

    A102,2

    A102,3

    A102,4,Y

    A102,5

    A102,6

    A102,7

    A103,1,Y

    A103,2

    A103,3,Y

    A103,4

    A103,5

    A103,6];

     

    NoConcatenate

    temp1:

    LOAD

    [Product Id],

    Rank,

    Flag,

    if(Flag='Y',alt(peek(Flag_count),0)+1,peek(Flag_count)) as Flag_count

    Resident Source

    Order by [Product Id];

     

    DROP Table Source;

     

    Final:

    LOAD

    [Product Id],

    Rank,

    if(odd(Flag_count),'Y',if(Flag='Y','Y','N')) as Flag

    Resident temp1;

     

     

    DROP Table temp1;

     

    HTH

    Sushil