Sum and group in load statement not working correctly
Hi, I know there are a number of threads that have been raised regarding aggregation in QV scripting and have looked through a lot of them to try and solve my issue.
I have a fairly decent size data set stored in a QVD (7.5 million rows).
I have written a load script to extract some of this data and aggregate it together.
My source fields are PositionId, ForecastDate, ForecastHours
on my initial load into a temp table I have -
PositionId, week(ForecastDate) as FWeek, ForecastHours
I then have a final table -
load PositionId, FWeek, sum(ForecastHours) as Fhours resident temp Group by PositionId, FWeek;
Drop table temp;
This should be as simple as can be, if I use a simple inline table as my source (with about 15 rows) I get the correct data out. However when I use my real data source my FWeek AND ForecastHours are both showing incorrect.
Hopefully someone has an idea of what I am doing wrong and how I can fix it.
As far as I am aware they are dates, if I just undertake the first load (temp table) the week(ForecastDate) function works correctly to return the relevant week number - would this work even with text value instead of date?