Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Try this:
load *,
if(ColumnX=1,ColumnY,peek(Continent)) as Continent
Inline [
ColumnX, ColumnY
1 , Asia
2 , Japan
2 , China,
3 , Beijing
];
HI,
Try this
Peek('ColumnY',0)
Best of luck
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.
Try this:
load *,
if(ColumnX=1,ColumnY,peek(Continent)) as Continent
Inline [
ColumnX, ColumnY
1 , Asia
2 , Japan
2 , China,
3 , Beijing
];
Thanks so very much Gysbert. Absolutely perfect! Just what I needed.