Table creation based on dates

Hi all!
Hope you can assist here, I have 3 tables with a lot of records for different employees. With this 3 tables I have to create a new one with a combination of the values based on dates
I understand the logic, but I am not able to “translate” into “knime language”

Table 1: employee and the start date /end date in a company

Table1
ee number end date start date company
0000023 31.10.2023 13.02.2023 Mokso
0000023 31.12.9999 01.11.2023 Tecny

Table 2: salay variation based on dates

Table2
ee number end date start date ann.salary
0000023 24.10.2023 13.02.2023 60000
0000023 17.03.2024 25.10.2023 62000
0000023 31.07.2024 18.03.2024 62500
0000023 31.12.9999 01.08.2024 65000

|Table3|||| simple- only with hire date
|—|—|—|—|
|ee number|end date|start date|hire date|
|0000023|31.12.9999|13.02.2023|13.02.2023|

This is what I need to create:

month ee number start date end date company annual salary hire date
Feb’23 0000023 13.02.2023 28.02.2023 Mokso 60000 13.02.2023
Mar’23 0000023 01.03.2023 31.03.2023 Mokso 60000 13.02.2023
Apr’23 0000023 01.04.2023 30.04.2023 Mokso 60000 13.02.2023
May’23 0000023 01.05.2023 31.05.2023 Mokso 60000 13.02.2023
Jun’23 0000023 01.06.2023 30.06.2023 Mokso 60000 13.02.2023
Jul’23 0000023 01.07.2023 31.07.2023 Mokso 60000 13.02.2023
Aug’23 0000023 01.08.2023 31.08.2023 Mokso 60000 13.02.2023
Sept’23 0000023 01.09.2023 30.09.2023 Mokso 60000 13.02.2023
Oct’23 0000023 01.10.2023 24.10.2023 Mokso 60000 13.02.2023
Oct’23 0000023 25.10.2023 31.10.2023 Mokso 62000 13.02.2023
Nov’23 0000023 01.11.2023 30.11.2023 Tecny 62000 13.02.2023
Dec’23 0000023 01.12.2023 31.12.2023 Tecny 62000 13.02.2023
Jan’24 0000023 01.01.2024 31.01.2024 Tecny 62000 13.02.2023
Feb’24 0000023 01.02.2024 28.02.2024 Tecny 62000 13.02.2023
Mar’24 0000023 01.03.2024 17.03.2024 Tecny 62000 13.02.2023
Mar’24 0000023 18.03.2024 31.03.2024 Tecny 62500 13.02.2023
Apr’24 0000023 01.04.2024 30.04.2024 Tecny 62500 13.02.2023
May’24 0000023 01.05.2024 31.05.2024 Tecny 62500 13.02.2023
Jun’24 0000023 01.06.2024 30.06.2024 Tecny 62500 13.02.2023
Jul’24 0000023 01.07.2024 31.07.2024 Tecny 62500 13.02.2023
Aug’24 0000023 01.08.2024 31.12.9999 Tecny 65000 13.02.2023

One entry per month where I can see the company and salary- for months where I have changes, I will have two rows with the different values.

Do you have any idea how to complete this action?

Thanks!

Hi @Mariaper ,

Firstly this isn’t a solution, but I thought that I’d post a workflow containing the above data as a starting point so people can get on with tackling the problem without having to reproduce the data again. :slightly_smiling_face:

I put it into a flow to have a think about the approach, and figured it would save time for others too.

Company - Employee - Dates.knwf (128.1 KB)

3 Likes

looks to me that you would rather want to translate it into a “sql language” using e.g. h2 db nodes. At a first glance i would probable start there
br

Thanks Daniel, do you mean with sql it will be more easy to solve than using tables in Knime?

Hi @Mariaper , I finally found some time to look at this. Here is one possible solution
Company - Employee - Dates - takbb.knwf (516.5 KB)

The initial part concerns itself with creating the rows for each period. The additional complication is a period ending 9999-12-31 where we don’t want to create every period up to then(!) so this has to be handled as a special case:

The “monthly” rows were derived by calculating the number of months between the first and last date for the whole period, and then using One Row to Many to create the required number of rows. Having then given each of these generated rows a sequential number, to use as an “offset”, the Date&Time Shift node can add this offset number of months to the start date to return the date for that new row. The first and last of the month is then derived…

I made use of a component of mine, “Date- Relative First and Last” a couple of times in the flow. It is used here return the first and last date of the month for a given date. This is used to populate the start and end for each period.

The rest of the flow is a little more straight forward. It joins to the other two tables to pick up the required additional columns

To perform a “join” that matches on a date range, it simply joins on “EE Number” to return all possible joins for the given EE number, and then uses a Rule Based Row Filter to reduce the actual resulting rows down to only those where the date ranges match between the tables.

After some tidying up, it returns this:

2 Likes