I think you just need to do a group by load using your relevant fields and then average the value, right?
The relevant fields are maybe all except value, Minute, and your date timestamps, but maybe you could remove some more fields that are not needed or just storing redundant information (file, file_name).
file, file_name, dir, Server, Year, Month, Monthyear, Date, Hour, Main_metric, sub_Metric, Metric,
avg(value) as AvgValue
Resident INPUT group by file, file_name, dir, Server, Year, Month, Monthyear, Date, Hour, Main_metric, sub_Metric, Metric;
where INPUT is your table loaded from qvd.
Thank you! That seems to have reduced over 8000K to 300K so is going to be great!
AVG: LOAD Server, Year, Month, Monthyear, Date, Day, Hour, Main_metric, sub_Metric, avg(value) as AvgValue FROM *_Perfmon_$(prevmonth).qvd (QVD) group by Server, Year, Month, Monthyear, Date, Day, Hour, Main_metric, sub_Metric;
As per your suggestion I also got rid of a lot of unwanted data, the work I am doing was started by a colleague then handed over to me and there seems to be a lot of Fields that are probably not wanted so I can probably reduce the size of the other original QVD's by removing the redundant data.
One question is it possible to combine the WHERE command with the LOAD? This would allow me to get rid of the Nulll values in that field?
I do ommit them from the final load in to my Report/App but it would be better to do it here so I'm not storing rubbish data.
AVGHOUR_Perfmon_processing.qvw 355.0 K