Date between two Dates

KNIME Analytics Platform
#1

Hi,

I'am a new Knime user...and found it very usefull....but

Allready a little challenge :

in one table, i import many information, and each one contain a Name (Salesname) and a Date (Referencedate).

In a second one, all Sales are referenced with a Team. But it always change and i identified it with start and end date.

Column :
StartDate
EndDate
SalesMan.X
Team Z

So for 1 Sales, many enties in the first table and also in the second one....

How to write in the first Table the right Team identifier, based on the refenceDate  !!???

I'm looking for something like (Java Snippet)


if ($StartDate$.compareTo($ReferenceDate$ ) <= 0) and
if ($EndDate$.compareTo($ReferenceDate$ ) >= 0)  and
if ($SalesMan.x$ == $SalesName {

---> Append a nem column (Team) in first table with value (TeamZ) from the second table

Many thanks for any help 

MitchP

#2

If I understand correctly, all you need is the Joiner node and input both tables, and in the node config choose to match up by ReferenceDate instead of RowID. You may want to choose Left Outer Join in the config as the type of join needed, i.e. all of the left table plus any matches from the right table.

Does this help?

Simon.

#3

Hi Simon,

Yes it help....

I did it so : right outer joint, so all entries in the second table match with the SalesName (SalesName <-> SalesMan.x) and to check if the ReferenceDate is between the Start and End Date with a snippet :

if ReferenceDate > StartDate and ReferenceDate < EndDate...new column with 1 or 0

after, Row filter and Cell Replacer...

Many thanks for the help !!

MitchP

#4

....oups, it doesn't work, "Duplicate search key"

 

#5

MitchP, I think you can try this

  1. loop over the team start and end dates and team names (e.g. 3 teams xyz) and with a variable to column note inject the table (e.g. 8 entries a to h). You now have a 24 row table that got sales date and start and end dates as well as team name and salesman.
  2. with a rule engine node, create your statement (sales date > star date) AND (sales date < end date), set the valid answer to 1 the unvalid answer to 0.
  3. with a filter rows node  remove the "0" (they do not match anyway)
  4. with a group by node, group by team name, aggregates with sales amount (sum)
  5. you now have a three rows table with the cumulated amount per team with team defined as time period

works quite nicely in my hands.

bruno

#6

Hi bruno,

Great....it's work the way you told me !!!

Many thanks