test

[Anonymous]

t

[maxgro]

Directory;

tmp:

LOAD Key,

     flag

FROM

TestData.xlsx

(ooxml, embedded labels, table is Sheet1);

tmp1:

load

  *,

  if(flag='Y', alt(peek(countflag),0)+1, Peek(countflag)) as countflag

Resident

  tmp

order by Key;

DROP Table tmp;

table:

NoConcatenate

load

  Key,

  //countflag,

  if(isnull(flag), if(Odd(countflag),'Y','N'), flag) as flag

Resident

  tmp1;

DROP Table tmp1;

[jerem1234]

Here's another way:

Directory;

Table:

LOAD Key,

     flag

FROM

TestData.xlsx

(ooxml, embedded labels, table is Sheet1);

Temp:

Noconcatenate Load Key, if(flag='Y' and peek(flag)='Y', 'E', if(flag='Y', 'Y', if(peek(flag)='Y', 'Y', 'N'))) as flag

Resident Table;

Final:

NoConcatenate Load Key, if(flag='E', 'Y', flag) as flag

Resident Temp;

Drop Table Table, Temp;

Hope this helps!