Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 -
temp:
Load
PositionId,
week(ForecastDate) as FWeek,
ForecastHours
From
c:\datasource\data.qvd (qvd);
I then have a final table -
final:
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.
Thanks
Chris
Hi @chrismtb ,
For ex: Might be one PositionId contains many years of data, and so same week numbers are falls under different years and aggregation doesn't satisfied with your requirement?
Can you try with WeekName() function? And verify once.
And also, you can share the sample data to check further..
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?
Hi @chrismtb ,
For ex: Might be one PositionId contains many years of data, and so same week numbers are falls under different years and aggregation doesn't satisfied with your requirement?
Can you try with WeekName() function? And verify once.
And also, you can share the sample data to check further..