App Development

[mikegrattan]

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.

[mato32188 · Accepted solution]

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

[mikegrattan]

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