Aggregating one field when values in another match.

[#1]

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.

[Gysbert_Wassenaar · Accepted solution]

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

[#3]

Find attached the QVW file.

[#4]

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