How to apply conditional Autofill

[#1]

Dear all,

Could you provide me the solution how to get a column Output?

First column = row number.

Second column 'Value' contains markers 'N'.

The condition should be:

if Value = N then the first three previous observations must be assigned with the row number where 'N' is found in column Value.

Column 'Output'  is the desired result.

Do you have a solution how to handle this?

Thanks and best regards,

Cornelis

[Gysbert_Wassenaar · Accepted solution]

Temp:

LOAD

     ID,

     Value

FROM

     ...source data...

     ;

Result:

LOAD

     ID,

     Value

     If(Peek(Value,-1)='N',  Peek(ID,-1),

          If(Peek(Value,-2)='N',  Peek(ID,-2),

               If(Peek(Value,-3)='N',  Peek(ID,-3))) as Output

RESIDENT

     Temp

ORDER BY

     ID desc

     ;

DROP TABLE Temp;

[#3]

Hi Gysbert,

Excellent, dat is the answer.

    If(Peek(Value,-1)='N',  Peek(ID,-1),

          If(Peek(Value,-2)='N',  Peek(ID,-2),

               If(Peek(Value,-3)='N',  Peek(ID,-3)))) as Output

Just a question:

is there an efficient script if you have e.g. 30 times

If(Peek(Value,-X)='N',  Peek(ID,-X),

i.e. X = 1, 2, 3.....30?

I suppose a loop is needed?

Best regards,

Cornelis

[Gysbert_Wassenaar]

I wouldn't do it that way with 30:

Result:

LOAD

     ID,

     Value,

     If(Previous(Value)='N', 1, rangesum(1,peek(Counter))) as Counter,

     If(Previous(Value)='N', Previous(ID),

          If(Peek(Counter)<30, Peek(Output))) as Output

RESIDENT

     Temp

ORDER BY

     ID desc

     ;

DROP TABLE Temp;

[#5]

Dear Gysbert,

Yes, that is what I'm looking for.

Previous and Peek are making the difference.

Thank you for your prompt answer.

A good learning point!

Best regards,

Cornelis