This is my first post here. I used Alteryx in the past in a consulting firm and now I’d like to introduce Knime in the company I currently work for, which is heavily excel-based…
In short, I am stuck with using a date_time_difference calculation based on “if” condition.
In detail, I need to calculate the rental days of a rental for a specific calendar month.
Here an example: imagine a car rental or hotel stay that have a start date [SD] and end date [ED] and I need to count the days “impacting” August, ie:
Case A SD 03AUG - ED 10AUG >>> rental days for AUG = 7 (ie date_time_diff 10AUG-03AUG)
Case B: SD 20JUL - ED 10 AUG >>> rental days for AUG = 9 (ie date_time_diff 10AUG-01AUG)
Case C SD 25AUG - ED 10SEP >>> rental days for AUG = 7 (ie date_time_diff 01SEP-25AUG)
Case D SD 20JUL - ED 10SEP >>> rental days for AUG = 31 (ie date_time_diff 01SEP-01AUG)
What I do in Excel:
It is a long but simple formula with many IF functions based on date difference that encompass the above 4 cases (in bold in the formula below) to calculate the rental days for a calendar month
J2 = start date
AZ2 = end date
CH3 = start of calendar month (ie 01AUG)
CH4 = end of calendar month + 1 (ie 01SEP)
What I would have done in Alteryx
I don’t have it installed it anymore, but if I recall well I would have used the formula tool using the DateTimeDiff function and IF THEN statement to replicate the Excel function above
What can I do in Knime?
Searching the forum I found mainly two cases for similar problems:
- use Date&Time Difference node and then the Rule Engine for conditions: but I get headache if I want to apply to my problem for the large number of columns I should create for each case (and consider that I have to do that for each of the 12 calendar months
- use the Column Expressions node, but I have found example of a syntax that I cannot understand or replicate
So, if the Column Expressions option is the best, can you give me an example of syntax for one of the cases above?
Or, is there any other node o “simple” combination of nodes that I can use?
thanks a lot!