topic Group By Date data load is not working in App Development

Group By Date data load is not working

mikegrattan — Mon, 27 Sep 2021 17:32:21 GMT

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.

Re: Group By Date data load is not working

mato32188 — Mon, 27 Sep 2021 19:16:45 GMT

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;

Re: Group By Date data load is not working

mikegrattan — Mon, 27 Sep 2021 20:06:17 GMT

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