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

[#1]

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.

[#2]

HI,

Try this

Peek('ColumnY',0)

Best of luck

[#3]

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.

[Gysbert_Wassenaar · Accepted solution]

Try this:

load *,

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

Inline [

ColumnX, ColumnY

1             ,  Asia

2             , Japan

2             , China,

3            , Beijing

];

[#5]

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