Hi everyone, Im new to KNIME. Im working with this kind of data and my purpose is that I want to find month (or months) needed to cover the ‘amount’.
I want to check if the value of months can cover the value of ‘amount’ column or not.
The flow is, I have to check the value of the latest month first, if it larger than the ‘amount’, return the name of that month (if it can be formatted to Date is perfect)
If the latest month can cover a part of ‘amount’, then check the previous month, then return the name of all the months that need to cover the ‘amount’
Edit: Data in Apr, Mar, Feb is Number (Double). I forgot to change it.
Welcome to KNIME Forum. This was not easy (at least for me ) . I hope it covers all your possible combinations, (I added some extra cases). So check the results with your real world data. See this wf looping_calculations.knwf (82.8 KB)
If you want to go the coding route, you can do this with one Column Expression node.
var cumulativeSum = 0
var colNames = []
var columnList = arrayRemove(columnNames(), 0, 1)
for (var i = 0; i < columnList.length; i++) {
cumulativeSum += column(columnList[i])
colNames.push(columnList[i])
if (cumulativeSum >= column("Amount")) {
break
}
}
result = colNames.join(", ")
This checks when the cumulative sum is higher than the amount and subsequently concatenates all the months it had to go through in order to satisfy the threshold.
I appreciate your solution a lot. I’m so impressed with your solution.
Your solution was very awesome, although it took me a while to understand haha. But so far it covered almost everything I want.
However, I found some minor mistake if the data unclear, the flow will be affected somehow.
I attached this excel file to broaden the situation that can be covered.
The thing is, I want to check to latest date first, then others will go after.
Take the Sum of latest date of that username. If it = 0, take the date after the latest date to test.
Compare the amount vs Sum. If amount <= Sum, write amount in the new column, else Sum (x)
If (x) exist, take amount - Sum of the date in (2.) and compare it with the Sum of the date after (2.). If [amount - Sum of the date in (2.)] <= [Sum of the date after (2.)] then write amount - Sum of the date in (2.), otherwise, write [Sum of the date after (2.)]. (y)
if claude else (y) of case (3.) exist, take amount - Sum of all previous date of that username and compare it with Sum the third date. if it <= Sum the third date, write [amount - Sum of all previous date of that username], write Sum of the third date.
Keep doing that until the sum of the amount in new column = the unique amount of each username
Date format: YYYY-MM-DD
<>
I hope you guys can support me on that.
Once again, I appreciate every contributions of you guys.
Hi @huynhduc , I’ve uploaded a workflow to the hub which is an alternative solution for the problem you first posed. I made use of the sample data set provided by @HansS in his uploaded workflow.
I’m trying to understand though how your latest post relates to the original question, and the solutions that have been provided, as the new data table is a totally different format to the first one. Can you elaborate on what you want.
If it turns out this is actually a new question, then please can you mark one of the posts above as the solution, and create a new question on the forum. By all means reference back to this discussion if it aids understanding. thanks
) . I hope it covers all your possible combinations, (I added some extra cases). So check the results with your real world data. See this wf 
