topic How to apply conditional Autofill in QlikView

How to apply conditional Autofill

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: How to apply conditional Autofill

Gysbert_Wassenaar — Fri, 19 Aug 2016 06:51:35 GMT

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;

Re: How to apply conditional Autofill

Fri, 19 Aug 2016 07:07:54 GMT

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

Re: How to apply conditional Autofill

Gysbert_Wassenaar — Fri, 19 Aug 2016 08:11:15 GMT

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;

Re: How to apply conditional Autofill

Sat, 20 Aug 2016 15:48:09 GMT

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