Hi everyone I have a table with data regarding sales per day and one table with the accounting close dates (which are diferent than the civil year). So I’d like to to add to my first table base on the day of the sale, the accounting dates (beginning and ending )/months and so on… My first aproach was to use a column expression node where I’d define the intervals of the accounting months, but this makes it “less pretty” lets say. And also since it’s manual errors are more likely to happen as if I use the first table this won’t happen. This is from a process that is assembled in SAS like this: proc sql; create table data_end_month as select distinct a.*, b.YearMonth_Account, b.Date_Start , b.Date_End, b.Date_Calendar_End from base1 as a left join Dates_close as b on b.Date_Start < a.Dt_Venda <= b.Date_End; quit; If anyone has any suggestions I’d appreciate it! Thanks :smiley:

I can’t vouch for its performance or efficiency, but if you have Python installed, along with the pandasql package, you can do your join in a relatively simple python script too. Simple example: [image] [image] [image] import pandasql as ps import pandas as pd sales_df=input_table_1 period_df=input_table_2 sql="select sales_df.[Sales Date], period_df.[Period] " \ + " from sales_df join period_df " \ + " on sales_df.[Sales Date] "\ + " between period_df.PeriodStart and period_df.PeriodEnd" output_table_1= ps.sqldf(sql, locals()) [image] Obviously the joined output could be whatever columns you want, but here I just return the period number for the given sales date. Joining Dates between.knwf (10.1 KB) [Edit: I’ve repackaged the python script as a component to make it easier to use generically. [image] See hub: [image] PandaSQL Join – takbb Joins two input tables using pandasql. The supplied query should reference the top input table as T1 and the lower input table as T2. T1 is the LEFT table in t… [image] Joining Dates between ... with python component – takbb Solutions for data science: find workflows, nodes and components, and collaborate in spaces.

Joiner with between

KNIME Analytics Platform

ipazin May 27, 2021, 8:07am 2

Hello @AnaMaximino,

check out this topic:

Br,
Ivan

4 Likes