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:
So, I need to fill Y's between Y's of a ProductId and N else where.
The required output is going to be
////////////////////////// Sample Code ///////////////////////////
LOAD * Inline
if(Flag='Y',alt(peek(Flag_count),0)+1,peek(Flag_count)) as Flag_count
Order by [Product Id];
DROP Table Source;
if(odd(Flag_count),'Y',if(Flag='Y','Y','N')) as Flag
DROP Table temp1;