Sum and group in load statement not working correctly

[chrismtb]

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

[PriyankaShivhare]

check your date parts if they are actually dates / hours or just the stringsin your database

[chrismtb]

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? 

[MayilVahanan · Accepted solution]

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..