I want to create an output, similar to attached file, which is made from an external file with its reference.
I tried to replicate it in Knime but couldn’t do it. Also, a logic is required which would whether the activity was completed or is it on-going depending on the current month. Like in row 2 in the output file.
Can anyone please help me with it?
Input 1.xlsx (8.7 KB)
Output.xlsx (10.2 KB)
To get the table structure you have in Output.xlsx, you will need to use the Concatenate node to concatenate multiple tables into one. As for the logic to see if it is currently ongoing, I recommend the Create Date&Time Range with settings so that it produces 1 row with the current execution time. Then you can extract the current year and month from it using the Extract Date&Time Fields node. Then you somehow need to transform the Aug-23 and others to a month number and year number as well (8 and 2023) so you can compare it to the current month and number. Based on that comparison, you can figure out if it is ongoing or not. However, going from Aug-23 to a numeric format is a little bit tricky. I would add a “dummy” day like so: 01. Aug-23 and then use the String to Date&Time node with the option “Date” instead of “Date&Time” and the string format
dd. MMM-yy to turn the text into a date object. Then you can extract month number and year again as extra columns using the Extract Date&Time Fields node. When you bring this together with the current date from above, you can do the logic you need with the Rule Engine node.
That’s a lot of stuff to go through, but I think it gives you a few pointers to follow. Let me know if you are stuck and I think I can help.