Using Peek method

[#1]

Key	Year	Value
A	2011	-
A	2012	-
A	2013	100
B	2011	-
B	2012	50
B	2013	-

I hava a table as shown above. Now I want that if 'Value' of current row does not exist, 'Value' of upper row should be copied if 'Key' of above row match with the current row's 'Key'. If it doesn't match, then itshould remain null. For the table given above, output table should be as follows:

Key	Year	Value
A	2011	-
A	2012	-
A	2013	100
B	2011
B	2012	50
B	2013	50

This should be done using Peek method, but I'm not able to use that method properly. Please help..

Thanks..!!

[Anonymous]

try this

Load

  Key,

  Year,

if (isnull(Value), if previous(Year)=Year-1 and not isnull(previous(Value)), previous(Value),Value) as Value

from xyc;

[MK_QSL]

Temp:

Load

  Key,

  Year,

  IF(IsNull(Value) or Len(Trim(Replace(Value,'-','')))=0,Null(),Value) as Value

Inline

[

  Key, Year, Value

  A, 2011, -

  A, 2012, -

  A, 2013, 100

  B, 2011, -

  B, 2012, 50

  B, 2013, -

];

NoConcatenate

Final:

Load

  Key,

  Year,

  If(Key = Previous(Key) and IsNull(Value),Previous(Value), Value) as Value

Resident Temp

Order By Key, Year;

Drop Table Temp;

[maxgro]

t:

LOAD Key, Year, Value

FROM [http://community.qlik.com/thread/133546] (html, codepage is 1252, embedded labels, table is @1);

final:

NoConcatenate

load

  Key, Year,

  if(peek(Key)=Key and len(trim(replace(Value,'-','')))=0, peek(Value),Value) as Value

Resident

  t

order by Key, Year;

DROP Table t;