topic Re: day sum from hour data in QlikView

day sum from hour data

Mon, 16 Mar 2015 15:23:58 GMT

Hi,

im loading large amounts of data from a database, and the data is too much detailed for the current task.
the data was created every hour, but only the sums of each day and month are needed.

The same group by has worked before on other projects and the editor shows no error, but when reloading the script, i just get a script error for the day_sums table.

Any ideas what could be the problem here ? The qv compiler is not helpfull (as always)

the testdata.xlsx is attached

Dummy_byhours:

LOAD source,

mon,

day,

hour,

parameter,

unit,

// sum(Daten),

DATA as DATA_h

FROM

.\test_data.xlsx

(ooxml, embedded labels, table is Tabelle1);

day_sums:

noconcatenate LOAD

source,

day,

sum(DATA_h) as DATA_d_sum, // script error here

unit

RESIDENT Dummy_byhours

GROUP by day, source

;

Re: day sum from hour data

Mon, 16 Mar 2015 15:27:59 GMT

GROUP by source;

Should be:

GROUP by day, source, unit;

Re: day sum from hour data

rubenmarin — Mon, 16 Mar 2015 15:28:33 GMT

Hi Andreas, you need to add 'unit' to the group by clause, or remove it from the LOAD.

Re: day sum from hour data

MK_QSL — Mon, 16 Mar 2015 15:28:43 GMT

add unit also in group by

Re: day sum from hour data

hic — Mon, 16 Mar 2015 15:30:03 GMT

Data:

LOAD source,

mon,

day,

parameter,

unit,

sum(DATA) as Data

FROM [.\test_data.xlsx] (ooxml, embedded labels, table is Tabelle1)

GROUP BY source, mon, day, parameter, unit;

HIC

Re: day sum from hour data

Vidit — Mon, 16 Mar 2015 15:41:09 GMT

Dummy_byhours:

LOAD source,
mon,
day,
hour,
parameter,
unit,
DATA as DATA_h
FROM

(ooxml, embedded labels, table is test);

day_sums:
noconcatenate
LOAD
source,
day,
sum(DATA_h) as DATA_d_sum, // script error here
unit

RESIDENT Dummy_byhours
GROUP by day, source ,unit
;

//drop if not required
drop table Dummy_byhours;