Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Become an analytics expert with Qlik's new 15 week course: Applied Data Analytics using Qlik Sense. READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
chrismtb
Contributor III
Contributor III

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 -

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

1 Solution

Accepted Solutions
MayilVahanan

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

Thanks & Regards,
Mayil Vahanan R

View solution in original post

3 Replies
PriyankaShivhare
Creator II
Creator II

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

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

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

Thanks & Regards,
Mayil Vahanan R

View solution in original post