topic Re: Using the keep function, how do I get the previous value whereby a certain criteria is met? in QlikView

Using the keep function, how do I get the previous value whereby a certain criteria is met?

Mon, 10 Dec 2012 11:10:40 GMT

I am reading data from excel. My data reads something like:

ColumnX, ColumnY

1 , Asia

2 , Japan

2 , China,

3, , Beijing.

So for instance - with Japan, if I want the continent I will say: keep(ColumnY) as Continent.

The problem is with China. How would I get Asia as the continent? I need to somehow use the keep with some type of 'where' criteria, where the last value = 1.

Maybe I can use a nested if such as if(keep(Columnx=1(Keep(Keep(Column1), the problem is I might have 30 countries in a row, all from the same continent.

Re: Using the keep function, how do I get the previous value whereby a certain criteria is met?

Mon, 10 Dec 2012 11:24:14 GMT

HI,

Try this

Peek('ColumnY',0)

Best of luck

Re: Using the keep function, how do I get the previous value whereby a certain criteria is met?

Mon, 10 Dec 2012 11:41:31 GMT

thanks. Not sure if I understand correctly - what if Asia is not the first record, for instance, it might read like:

ColumnX, ColumnY

1 , Europe

2 , Germany,

2 , France,

3 , Paris

1 , Asia

2 , Japan

2 , China,

3, , Beijing.

So for instance, above - I would like Europe returned with Germany and France and Asia with Japan and China.

Thanks.

Re: Using the keep function, how do I get the previous value whereby a certain criteria is met?

Gysbert_Wassenaar — Mon, 10 Dec 2012 11:43:15 GMT

Try this:

load *,

if(ColumnX=1,ColumnY,peek(Continent)) as Continent

Inline [

ColumnX, ColumnY

1 , Asia

2 , Japan

2 , China,

3 , Beijing

];

Re: Using the keep function, how do I get the previous value whereby a certain criteria is met?

Mon, 10 Dec 2012 12:05:18 GMT

Thanks so very much Gysbert. Absolutely perfect! Just what I needed.