populate the data for missing years

[Navars]

Hello Everyone,

i have a requirement like, i have 6 years data in that couple of years data not there, for missing years data need to populate the average of other years data.

data is like below , here 2015 and 2018 are missing for that two years i need to populate the sales as 300 (average of remaining Years sales)

Year, sales
2020,100
2019,200
2017,300
2016,400
2014,500

Thanks in Advance...

[stevejoyce · Accepted solution]

Try this:

data:
load * inline [
Year, sales
2020,100
2019,200
2017,300
2016,400
2014,500
]
;

//get average of all years and store in variable
data_avg_sales:
load avg(sales) as avg_sales
resident data;

let vavgsales = peek('avg_sales', 0, 'data_avg_sales');

//get min max years to fill in all years
data_min_max_year:
load max(Year) as maxYear
,min(Year) as minYear
resident data
;

let vmaxyear = peek('maxYear', 0, 'data_min_max_year');
let vminyear = peek('minYear', 0, 'data_min_max_year');

all_years:
load
$(vminyear) + rowno() as Year_all
autogenerate($(vmaxyear) - $(vminyear));

//for missing years, set sales to average sales
concatenate(data)
load
$(vavgsales) as sales,
Year_all as Year
resident all_years
where not exists(Year ,Year_all)
;

//cleanup
drop tables all_years, data_min_max_year, data_avg_sales;

exit script;