Aggregation Based on Specific Number of Rows

Hello everyone,

I am almost done with a project that I have been working on and I wouldn’t have reached so far without the support of the community. Unfortunately I am currently stuck in the following:

I have an input in the format below:
Input

And I would like to generate the following output:
Output

Basically I need to sum the 1st 3 weeks of each item and assign the sum to a new column and sum the 1st 5 weeks of every item and assign the sum in a new column. The data is already sorted based on weeks so no need for sorting.

I tried with pivoting but unfortunately I wasn’t able to reach the desired output. Can someone help me with such a workflow?

Thanks a lot in advance.

Input.xlsx (6.0 KB)
Output.xlsx (12.5 KB)

Hi @amrzahran -

No pivoting needed here - just a bit of filtering and GroupBy.

2019-08-14%2015_05_28-KNIME%20Analytics%20Platform

RowFilterGroupByExample.knwf (22.2 KB)

2 Likes

@ScottF Thanks a lot for your time and your reply. Unfortunately the input data (weeks) column doesn’t always have the values 1-6.

It might have 10-99 or any values basically so is it possible to modify the workflow to filter the 1st (3) and (5) rows regardless of the numbers stated in the (weeks) column? The weeks column is always sorted in ascending order.

That’s how the workflow works now. The 3 / 5 row filtering is independent of how many values there are in the weeks column.

Oh, wait, I think I see what you mean now. Let me think about it.

@ScottF Please have a look at the modified input below.
Modified%20Input

As you can see the weeks value don’t have to be 1 to 6. It might take any other values.

What is guaranteed is that it is always arranged in ascending order and for every item there are the same number of weeks (no. of rows for every item) regardless of the week numbers.

I hope it is a bit more clear now.

Thanks a lot.

How about this? It counts the number of items in each group, and then filters on the count, ignoring the week entirely.

RowFilterGroupByExample.knwf (32.1 KB)

3 Likes

Flawless! Works perfectly. Thanks so much :slight_smile:

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.