topic Auto-fill backward under conditions in QlikView

Auto-fill backward under conditions

Wed, 25 Nov 2020 16:16:04 GMT

Dear all,

I have here a script question.

The first 3 columns are input values.

Column Gradient needs to be expanded with column Output as results of script calculation.

Column Gradient is based on the sum of Distance, always <= 500 meter.

Let's consider ID = 5. The sum of Distance is equal 350 meter and so all cells DI 1-5 is equal to 12.

Now for ID = 17.

Gradient = 20. The sum of Distance for ID = 17, 16, 15, 14, 13 = 476 and agree with the requirement that sum < 500. So, the cells must be filled with 20 for ID = 17, 16, 15, 14, 13.

Table:
LOAD RowNo() as ID, * inline
[
Distance, Gradient
0
100
201
305
350,12
550
600
700,10.5
982
1199,15
1400
1485
1535
1596
1699
1800
2011,20
2239
2399
2600
];

Table2:
NoConcatenate LOAD *,
if (previous(Gradient) > 0, Previous(Gradient)) as Output
Resident Table Order by ID desc;

Could you please provide me the solution? I think you need peek function with Rangesum but I do not know how to solve this.

Best regards,

Cornelis

Re: Auto-fill backward under conditions

MarcoWedel — Mon, 20 Feb 2017 23:14:47 GMT

Hi,

one solution might be:

Table:
LOAD RowNo() as ID, * Inline [
Distance, Gradient
0
100
201
305
350,12
550
600
700,10.5
982
1199,15
1400
1485
1535
1596
1699
1800
2011,20
2239
2399
2600
];

Join
LOAD ID,
    If(IsNum(Gradient),Distance,Peek(LastDistWithGrad)) as LastDistWithGrad,
    Alt(Gradient,If(Peek(LastDistWithGrad)-Distance<=500,Peek(Output))) as Output
Resident Table Order by ID desc;

DROP Field LastDistWithGrad;

hope this helps

regards

Marco

Re: Auto-fill backward under conditions

Tue, 21 Feb 2017 05:34:03 GMT

Dear Marco,

This is very good. I didn't realize that Alt function is a form of extended IF. A good learning point

Best regards,

Cornelis