topic Re: Aggregating one field when values in another match. in QlikView

Aggregating one field when values in another match.

Wed, 03 Apr 2013 14:04:48 GMT

Hello gentlemen,

I have the following problem:

there's an ID field of sorts in the table and fields with date and values.

ID repeats across dates and values, but date+value fields do not repeat.

I need to aggregate all this within the loading script so that I get one line for every ID and date (month, specifically) combo with the sum of all associated values.

There's so many values that this needs to be done in the loading script, not the UI.

Included is an excel file with the how the source table looks and how it's supposed to look after aggregation.

Re: Aggregating one field when values in another match.

Gysbert_Wassenaar — Wed, 03 Apr 2013 14:27:05 GMT

Something like:

T1:

load ID, Month, sum(Sum) as Sum

group by ID, Month;

LOAD ID,

MonthName(Date) as Month,

Sum

FROM aggregation.xlsx (ooxml, embedded labels, table is Source);

See attached example

Re: Aggregating one field when values in another match.

Wed, 03 Apr 2013 14:36:52 GMT

Find attached the QVW file.

Re: Aggregating one field when values in another match.

Wed, 03 Apr 2013 14:48:32 GMT

Thank you Gysbert, it looks like group by is what I was looking for.