This thread is for posting solutions to “Just KNIME It!” Challenge 5. Feel free to link your solution from KNIME Hub as well!
Instead of using flow variables, I took a different approach which avoids having to loop. In my opinion, it is much faster.
Here’s my solution:
@alinebessa , I made a couple assumptions:
First assumption was that I’m “restricted” to what the lookup data has. Base on this assumption, some of the Contract dates would not have a fiscal year (basically any contract dates before 2015-10-01). I still included these dates, but they will have no Fiscal Year assigned.
Contract Dates data has header “Contract Start Date”. I expected just date instead of “start” date. Second assumption is that duration of contract does not, so I treat the contract start dates just as the date the contract was executed. Fiscal period is what matters, and indeed has both start and end dates.
Here is my solution
it uses a combination of joins and filters - no SQL.
[Challenge5_fiscalyear – KNIME Hub]
assuming that flexiibility was a requirement, here’s my solution.
I, too, made assumptions and tried to build a very simple workflow:
- Fiscal Year lookup table was an example. The solution needed to determine which fiscal year the contract fell into based on the Contract Start Date ONLY.
- There are no Contract End Dates so there’s no way to determine the length of the contract in days, for example.
KNIME_Challenge5_Fiscal_Years.knwf (21.6 KB)
My solution requires no flow variables, either.
Workflow to add the corresponding fiscal year based on contract date. The workflow allows selecting the fiscal start date.
here is my solution.
Here is my solution to #justknimeit-5 :
KNIME Hub > gonhaddock > Spaces > Just_KNIME_It > Just KNIME It _ Challenge 005
Hi @lenexa_jayhawk , can you upload your solution on the hub instead? That’s how it’s preferred as it can easily be viewed without having to download and import your workflow
I made it pretty easy. Just using starting month from fiskal year to decide, if I have to add 1 to the current year. No Loops necessary.
Here is my solution : KNIME_Challenge-5 – KNIME Hub
→ I just used the fiscal year lookup table to generate my own fiscal year reference table with two date creator nodes, in order to be more flexible and scalable, and avoid rows without fiscal year.
→ I also added an ID column, with no particular sense, but just to identify the different rows and contracts at the end.
→ I also used one loop, with filtering inside loop to avoid duplicate filtering on the big processed table after the loop (here, due to the low number of rows, the duplicate filtering can be done after the loop, but in case of a lot of rows, the loop will just generate a lot of useless rows that will slower the process).
Here’s my solution to it.
While I dealt quite a bit with contracts in the past one thing that was important to me was to make sure that NO duplicate entry was deleted because it is very well possible that different contracts start on the same day. So I decided to give each line a unique / temporary contract ID.
In addition, I also wanted to have a dynamically created flow variable that always holds the earliest date from the fiscal years table. I know quite a few cases where we always look back 3 or - in this case - 5 years. So rather than having the user to create these manually by adjusting e. g. the Rule Engine Node.
That was a real nice challenge
here’s my solution contracts by fiscal year – KNIME Hub
Here is my solution.
Here is my solution: