Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mikegrattan
Creator III
Creator III

Group By Date data load is not working

I have the following data load script, and I'm trying to summarize (Average) the data by Date and station.  The source data in the QVD file is stored by DateTime stamp, so there will be multiple records per day and station, so the goal here is to get an average temperature per day by station and an average humidity per day by station.

LOAD
Date,
station,
Avg(relative_humidity) as Humidity,
Avg(temp_f) as Temp
FROM [lib://QVDFiles (tasp-qlik_administrator)/QVDFiles/MyQVDFile.qvd](qvd)
Where Date >= '1/1/2018'
Group by Date,station;

The data load, however, is not being properly grouped and averaged correctly; as you can see in this screen shot, there are multiple duplicate entries by Date and station, so it's apparent to me that the Group By load is not working as expected.

mikegrattan_0-1632763833873.png

 

 

 

1 Solution

Accepted Solutions
mato32188
Specialist
Specialist

Hi @mikegrattan ,

just making sure that your date field is really date without timestamp remainder.

LOAD
floor(Date) as Date,
station,
Avg(relative_humidity) as Humidity,
Avg(temp_f) as Temp
FROM [lib://QVDFiles (tasp-qlik_administrator)/QVDFiles/MyQVDFile.qvd](qvd)
Where Date >= '1/1/2018'
Group by floor(Date),station;

Br

m

ECG line chart is the most important visualization in your life.

View solution in original post

2 Replies
mato32188
Specialist
Specialist

Hi @mikegrattan ,

just making sure that your date field is really date without timestamp remainder.

LOAD
floor(Date) as Date,
station,
Avg(relative_humidity) as Humidity,
Avg(temp_f) as Temp
FROM [lib://QVDFiles (tasp-qlik_administrator)/QVDFiles/MyQVDFile.qvd](qvd)
Where Date >= '1/1/2018'
Group by floor(Date),station;

Br

m

ECG line chart is the most important visualization in your life.
mikegrattan
Creator III
Creator III
Author

Ah, yes, the Floor() function!  Thanks, that worked.