topic Re: Aggregation while loading table in QlikView

Aggregation while loading table

Wed, 19 Nov 2014 11:49:33 GMT

I have a script like this:

LOAD SHOPID,

KostenartID,

Date,

Key,

ValueIst,

ValueForecast,

ValuePlan2015

FROM Table1;

And the result will be a table with too many rows, because the values (last 3 columns) are located in different rows.

The first 4 colums have the same keys. Now I do not want to load 3 rows but 1 row, because the keys are the same.

How can I change the scriot to get a table like this

Thanks in advance

Thomas

Re: Aggregation while loading table

Wed, 19 Nov 2014 11:53:42 GMT

Hi Thomas

use them in expression not in dimension.

and use the avg or sum formula, as it suits you

Regards

harshita Gaur

Re: Aggregation while loading table

jonathandienst — Wed, 19 Nov 2014 11:55:22 GMT

Assuming that the 'other' row's values are always 0 or null, then you could do this:

LOAD SHOPID,

KostenartID,

Date,

Key,

Sum(ValueIst) As ValueIst,

Sum(ValueForecast) As ValueForecast,

Sum(ValuePlan2015) As ValuePlan2015

FROM Table1

Group By

SHOPID,

KostenartID,

Date,

Key;

HTH

Jonathan

Re: Aggregation while loading table

Wed, 19 Nov 2014 11:57:50 GMT

Try this;

LOAD SHOPID,

KostenartID,

Date,

Key,

sum(ValueIst) as ValueIst,

sum( ValueForecast) as ValueForecast,

maxstring( ValuePlan2015) as ValuePlan2015

FROM Table1

group by

SHOPID,

KostenartID,

Date,

Key

;

Good Luck

Re: Aggregation while loading table

Wed, 19 Nov 2014 11:58:41 GMT

Hi,

Use sum(valueforecast) and other 2 in expression. But suppressing at script level is best.

Regards,

Navdeep