topic Re: Using Peek method in QlikView

Using Peek method

Tue, 16 Sep 2014 13:42:48 GMT

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..!!

Re: Using Peek method

Anonymous — Tue, 16 Sep 2014 13:51:18 GMT

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;

Re: Using Peek method

MK_QSL — Tue, 16 Sep 2014 13:58:25 GMT

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;

Re: Using Peek method

maxgro — Tue, 16 Sep 2014 14:20:27 GMT

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

order by Key, Year;

DROP Table t;