Qlik Community

QlikView Documents

Documents for QlikView related information.

Filling values between start and end point in data table

sushil353
Honored Contributor II

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

Attachments
Version history
Revision #:
1 of 1
Last update:
‎08-22-2014 02:07 AM
Updated by: