Dates and group by

KNIME Analytics Platform
1

I have a periods in second table and I want to know If date in first table belong to one of the dates in first table then calculate the sum of A and B.
we

fsd

2

Hi there @pjacob,

What is your expected output? Sum per periods added in second table or? Does Id column matters in this process?

Br,
Ivan

3

@ipazin

Zrzut ekranu (29)

4

Hi @pjacob,

if tables are not big you can first use Cross Joiner, then Rule-based Row Filter to remove rows where date is not in period followed by GroupBy node with date_start and date_end as grouping columns and two Sum aggregations (one for A and one for B column). If necessary, using Joiner join 2 sum columns (inner join based on period columns) back to second table.

If tables are big (meaning Cross Joiner could take time to execute and will create huge table) you can use loop. Start with Table Row To Variable Loop Start from second table, then again using Rule-based Row Filter filter first table (but using flow variables this time) followed by GroupBy but this time without grouping columns (this will sum entire table). Close loop with Loop End and again join to second table using Joiner (inner join based on Row ID).

If any problems feel free to ask. Also I can share workflow example.

Hope this helps!

Br,
Ivan

2 Likes
5

@ipazin Hi :slight_smile: Thanks for answer. Could you show me second method in knime because I don’t fully understand how to do this? :smiley:

1 Like
6

Hi @pjacob,

sure. Here is print screen:

Loop&Filter

Instead of dates I used simple Integers but flow should be same.

Here is workflow:
2020_03_27_Table_Row_to_Variable_Loop_With_Filtering.knwf (23.0 KB)

If any questions feel free to ask.

Br,
Ivan

2 Likes
7

@ipazin what if i have a bigger tables with more Id and how can I take this solution in this? e.g.
asdasd
and same with table 1 :smiley:

8

Hi there @pjacob,

that’s why I asked you does Id column matters in first reply :smiley:

Either follow first approach (with Cross Joiner) and modify Rule-based Row Filter so it does also filter rows not having same id (for big tables make use of KNIME streaming functionality as both nodes are streamable) either make inner join using Joiner node on Id column and continue with Rule-based Row Filter just on periods…

Br,
Ivan

9

@ipazin thank you very much :smiley:

1 Like
10

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