topic Sum and group in load statement not working correctly in QlikView

Sum and group in load statement not working correctly

chrismtb — Sun, 25 Oct 2020 17:06:11 GMT

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

Re: Sum and group in load statement not working correctly

PriyankaShivhare — Mon, 26 Oct 2020 01:33:51 GMT

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

Re: Sum and group in load statement not working correctly

chrismtb — Mon, 26 Oct 2020 01:55:01 GMT

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?

Re: Sum and group in load statement not working correctly

MayilVahanan — Mon, 26 Oct 2020 04:15:03 GMT

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