Averaging in script

[#1]

Hi...

When loading a table (Plan.txt), with the following sample data:

SKU             Week               Plan

A                   45                   100

A                   46                   200

A                   47                   150

A                   48                   100

A                   49                   300

A                   50                   100

B                   45                   400

B                   46                   500

B                   47                   450

B                   48                   400

B                   49                   300

B                   50                   200

I need to generate a. qvd (Plan.qvd), from the previous table, with Plan averages of the following 3 weeks of each SKU. For example:

SKU             Week               Plan        AVGPlan

A                   45                   100          150

A                   46                   200          183,33

A                   47                   150          166,66

A                   48                   100          133,33

A                   49                   300            33,33

A                   50                   100              0

B                   45                   400          450

B                   46                   500          383,33

B                   47                   450          300

B                   48                   400          166,66

B                   49                   300            66,66

B                   50                   200              0

Note: All this do I need to script level. Therefore, I can not do it with any graphics

Thanks

[johnw · Accepted solution]

Here's one way.

Raw:
LOAD * INLINE [
SKU,Week,Plan
A,45,100
A,46,200
A,47,150
A,48,100
A,49,300
A,50,100
B,45,400
B,46,500
B,47,450
B,48,400
B,49,300
B,50,200
];

New:
NOCONCATENATE
LOAD
SKU
,Week
,Plan
,rangesum(if(SKU=peek(SKU,-1),peek(Plan,-1))
         ,if(SKU=peek(SKU,-2),peek(Plan,-2))
         ,if(SKU=peek(SKU,-3),peek(Plan,-3)))/3 as AvgPlan
RESIDENT Raw
ORDER BY SKU, Week DESC
;

DROP TABLE Raw;