Calculating sum of quantities comparing dates of two tables

KNIME Analytics Platform
1

Hello,

I have the below input tables:

tables

For each person I want to apply the below procedure:

  • Sum the quantity at the table-2, where the the date-2 is smaller than date-1

  • For example, Jennifer has the date 10.06.2019 at row2 at the table-1. From table-2 find the rows which are less than 10.06.2019 and sum the all quantities at these rows. For that case, row 1 ,2 and 3 has smaller dates than 10.06.2019. So sum of these quantities are 14.

As a result the output table should be as below:

output table

Workflow is as below:

KNIME_create _table.knwf (6.5 KB)

I will be very glad for any help. Thanks a lot.

2

This should get you started:
https://hub.knime.com/elsamuel/spaces/Public/latest/KNIME_create_table

The values for Person and Date-1 from Table 1 are converted to variables and used for filtering. A GroupBy node calculates the sums.

3 Likes
3

@elsamuel, can you please share the work flow? I am stuck at the date filter node. Thanks.

4

I did share the workflow in my last response. It’s accessible and downloadable from my Hub space.

Is the link not working?

5

@elsamuel, ok. It is my fault. Thanks a lot for giving your time.

6

Hi @elsamuel,

it is better to use Short Links when linking Hub workflows as this link is not supposed to change even if you change flow location to let’s say different space :wink:

Br,
Ivan

7

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