4 Replies Latest reply: Dec 10, 2012 7:05 AM by craigjames

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

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?

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?

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?

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?

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