Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello all Experts.
I have a table with the columns : Date, Asset, Open, Close ,High. Low .
in the table I ave more then 200 Asset.
for each asset I have year of daily data.
Date | Asset | Open | Close | High | Low | Avg14 |
---|---|---|---|---|---|---|
I need for each row of date include avg(Close) for the last 14 days of the asset.
I need to do this on the script level.
Does anyone have an Idea?
Ariel
try this
// test data, 1 year, 200 asset
Tmp:
load 'Asset ' & num(rowno(), '000') as Asset2 AutoGenerate 200;
join (Tmp) load date(makedate(2015) + rowno()-1) as Date2 autogenerate 365;
Asset2: NoConcatenate load Asset2, Date2, floor(rand()*10+1) as Value2 Resident Tmp;
DROP Table Tmp;
// load order by, peek to get previous 13 values
Final2:
load *,
rangeavg(
Value2,
if(Peek(Asset2)=Asset2, Peek(Value2)) ,
if(Peek(Asset2,-2)=Asset2, Peek(Value2,-2)) ,
if(Peek(Asset2,-3)=Asset2, Peek(Value2,-3)) ,
if(Peek(Asset2,-4)=Asset2, Peek(Value2,-4)) ,
if(Peek(Asset2,-5)=Asset2, Peek(Value2,-5)) ,
if(Peek(Asset2,-6)=Asset2, Peek(Value2,-6)) ,
if(Peek(Asset2,-7)=Asset2, Peek(Value2,-7)) ,
if(Peek(Asset2,-8)=Asset2, Peek(Value2,-8)) ,
if(Peek(Asset2,-9)=Asset2, Peek(Value2,-9)) ,
if(Peek(Asset2,-10)=Asset2, Peek(Value2,-10)) ,
if(Peek(Asset2,-11)=Asset2, Peek(Value2,-11)) ,
if(Peek(Asset2,-12)=Asset2, Peek(Value2,-12)) ,
if(Peek(Asset2,-13)=Asset2, Peek(Value2,-13))
) as AvgValue2 ,
rangesum(
Value2,
if(Peek(Asset2)=Asset2, Peek(Value2)) ,
if(Peek(Asset2,-2)=Asset2, Peek(Value2,-2)) ,
if(Peek(Asset2,-3)=Asset2, Peek(Value2,-3)) ,
if(Peek(Asset2,-4)=Asset2, Peek(Value2,-4)) ,
if(Peek(Asset2,-5)=Asset2, Peek(Value2,-5)) ,
if(Peek(Asset2,-6)=Asset2, Peek(Value2,-6)) ,
if(Peek(Asset2,-7)=Asset2, Peek(Value2,-7)) ,
if(Peek(Asset2,-8)=Asset2, Peek(Value2,-8)) ,
if(Peek(Asset2,-9)=Asset2, Peek(Value2,-9)) ,
if(Peek(Asset2,-10)=Asset2, Peek(Value2,-10)) ,
if(Peek(Asset2,-11)=Asset2, Peek(Value2,-11)) ,
if(Peek(Asset2,-12)=Asset2, Peek(Value2,-12)) ,
if(Peek(Asset2,-13)=Asset2, Peek(Value2,-13))
) as SumValue2
Resident Asset2
order by Asset2, Date2;
DROP Table Asset2;