Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Aggregating one field when values in another match.

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.

1 Solution

Accepted Solutions
Highlighted
MVP & Luminary
MVP & Luminary

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


talk is cheap, supply exceeds demand

View solution in original post

3 Replies
Highlighted
MVP & Luminary
MVP & Luminary

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


talk is cheap, supply exceeds demand

View solution in original post

Highlighted
Not applicable

Find attached the QVW file.

Highlighted
Not applicable

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