Convert complex Excel function into KNIME

KNIME Analytics Platform
1

Hi everyone, I have built this excel function and it I don’t know how to convert it to KNIME.

Here is my data, and the column Refund is my desired result also the result of this formula, starting from row 2:
=IF(C2-SUM(FILTER(F:F,A:A=A2,E:E>=E2))>=0,F2,IF(C2-SUM(FILTER(F:F,A:A=A2,E:E>=E2))+F2>=0,C2-SUM(FILTER(F:F,A:A=A2,E:E>=E2))+F2,0))

user_name Code amount Ref number Date Total Refund
Vietnam 372449446 10,000,000 2551 2024-05-02 10,000,000 10,000,000
Vietnam 372449446 10,000,000 105373 2024-03-03 200,000 0
United States 1109683890 10,000,000 2725 2024-05-02 70,000,000 10,000,000
United Kingdom 541673807 10,000,000 2619 2024-05-02 10,000,000 10,000,000
United Arab Emirates 904448505 10,000,000 3170 2024-05-02 10,000,000 10,000,000
Turkey 603732558 12,000,000 2830 2024-05-02 10,000,000 10,000,000
Turkey 603732558 12,000,000 68901 2024-02-04 200,000 200,000
Thailand 1036127587 10,000,000 3157 2024-05-02 10,000,000 10,000,000
Spain 1008863264 10,300,000 2673 2024-05-02 10,000,000 10,000,000
Spain 1008863264 10,300,000 141975 2024-04-02 600,000 300,000
South Africa 645067822 10,000,000 3377 2024-05-02 10,000,000 10,000,000
Singapore 903750383 9,800,000 2743 2024-05-02 10,000,000 9,800,000
Saudi Arabia 549777848 9,600,000 2790 2024-05-02 10,000,000 9,600,000
Russia 1041407098 10,000,000 2744 2024-05-02 10,200,000 10,000,000
Russia 1041407098 10,000,000 68704 2024-02-04 1,600,000 0
Philippines 829374517 10,000,000 2900 2024-05-02 10,000,000 10,000,000
Peru 44808261 10,000,000 2701 2024-05-02 10,000,000 10,000,000
Pakistan 311832019 10,000,000 2896 2024-05-02 10,000,000 10,000,000
Nigeria 256445542 20,000,000 2336 2024-05-02 20,000,000 20,000,000
Nigeria 256445542 20,000,000 105040 2024-03-03 200,000 0
Mexico 613539683 10,000,000 3222 2024-05-02 10,000,000 10,000,000
Malaysia 670405651 10,000,000 3136 2024-05-02 10,000,000 10,000,000
Laos 962880062 10,000,000 3000 2024-05-02 10,000,000 10,000,000
Korea 1043947886 20,000,000 2669 2024-05-02 20,000,000 20,000,000
Korea 1043947886 20,000,000 141962 2024-04-02 600,000 0
Kenya 1014413172 200,000 2952 2024-05-02 200,000 200,000
Japan 293754452 10,000,000 3253 2024-05-02 20,000,000 10,000,000
Japan 293754452 10,000,000 68922 2024-02-04 400,000 0
Italy 1158583813 10,000,000 3218 2024-05-02 10,000,000 10,000,000
Indonesia 358057353 20,000,000 2993 2024-05-02 20,000,000 20,000,000
India 53831405 10,000,000 2836 2024-05-02 10,600,000 10,000,000
Germany 226630716 10,000,000 2421 2024-05-02 10,000,000 10,000,000
France 58239472 10,000,000 3201 2024-05-02 10,000,000 10,000,000
Egypt 598833157 26,000,000 2342 2024-05-02 20,000,000 20,000,000
Egypt 598833157 26,000,000 141676 2024-04-02 7,200,000 6,000,000
Egypt 598833157 26,000,000 105046 2024-03-03 200,000 0
Colombia 158649922 300,000 105296 2024-03-03 200,000 200,000
Colombia 158649922 300,000 68849 2024-02-04 200,000 100,000
China 15276633 200,000 3289 2024-05-02 200,000 200,000
Chile 151470 10,000,000 3372 2024-05-02 10,000,000 10,000,000
Canada 897852173 10,000,000 3037 2024-05-02 20,000,000 10,000,000
Cambodia 600526694 10,000,000 3306 2024-05-02 10,000,000 10,000,000
Brazil 466493113 10,000,000 2899 2024-05-02 10,000,000 10,000,000
Bangladesh 1155821483 10,000,000 2809 2024-05-02 10,000,000 10,000,000
Australia 266622764 10,000,000 2950 2024-05-02 10,000,000 10,000,000
Argentina 55053875 10,000,000 2606 2024-05-02 10,000,000 10,000,000

If you see this content among my posts, it’s correct. Because I am new to KNIME, I try to learn all the stuff that KNIME can do haha. So if you don’t mind, please supporting me on my learning KNIME journey.
Thank you everyone. Hope to see your idea.

2

Steps

  1. Excel Reader
  2. String to Date/Time node - if needed to convert sting column to Date&Time data type
  3. Group Loop Start - (group by ‘Code’)
  4. Sorter Node (sort by ‘Date’)
  5. Moving Aggregation- (cumulative sum of ‘Total’)
    6.Java Snippet - (calculate refund)
  6. Loop End
  7. Joiner Node (if needed)

use this in Java Snippet Node

double refund;
double remainingAmount = c_amount - c_MATotal;
if (remainingAmount >= 0) {
refund = c_Total;
} else if (remainingAmount + c_Total >= 0) {
refund = c_amount - c_MATotal + c_Total;
} else {
refund = 0;
}
out_Refund = refund;

Below is attached workflow…Make changes as per your requirment
QnA2.knwf (83.3 KB)

3

Hi @yogesh_nawale

Thanks for reply me. However, when I downloaded your workflow and ran it, the result was not as I showed.

See case Egypt.

Can you check it again and let me know what should I check or change please.

user_name Code amount Ref number Date Total Refund
Spain 1008863264 10000000 2673 2024-05-02 10000000 10000000
Spain 1008863264 10000000 141975 2024-04-02 600000 600000
Kenya 1014413172 200000 2952 2024-05-02 200000 200000
Thailand 1036127587 10000000 3157 2024-05-02 10000000 10000000
Russia 1041407098 10000000 2744 2024-05-02 10200000 10000000
Russia 1041407098 10000000 68704 2024-02-04 1600000 1600000
Korea 1043947886 20000000 2669 2024-05-02 20000000 20000000
Korea 1043947886 20000000 141962 2024-04-02 600000 600000
United States 1109683890 10000000 2725 2024-05-02 70000000 10000000
Bangladesh 1155821483 10000000 2809 2024-05-02 10000000 10000000
Italy 1158583813 10000000 3218 2024-05-02 10000000 10000000
Chile 151470 10000000 3372 2024-05-02 10000000 10000000
China 15276633 200000 3289 2024-05-02 200000 200000
Colombia 158649922 200000 68849 2024-02-04 200000 200000
Colombia 158649922 200000 105296 2024-03-03 200000 200000
Germany 226630716 10000000 2421 2024-05-02 10000000 10000000
Nigeria 256445542 20000000 2336 2024-05-02 20000000 20000000
Nigeria 256445542 20000000 105040 2024-03-03 200000 200000
Australia 266622764 10000000 2950 2024-05-02 10000000 10000000
Japan 293754452 10000000 3253 2024-05-02 20000000 10000000
Japan 293754452 10000000 68922 2024-02-04 400000 400000
Pakistan 311832019 10000000 2896 2024-05-02 10000000 10000000
Indonesia 358057353 20000000 2993 2024-05-02 20000000 20000000
Vietnam 372449446 10000000 2551 2024-05-02 10000000 10000000
Vietnam 372449446 10000000 105373 2024-03-03 200000 200000
Peru 44808261 10000000 2701 2024-05-02 10000000 10000000
Brazil 466493113 10000000 2899 2024-05-02 10000000 10000000
India 53831405 10000000 2836 2024-05-02 10600000 10000000
United Kingdom 541673807 10000000 2619 2024-05-02 10000000 10000000
Saudi Arabia 549777848 9600000 2790 2024-05-02 10000000 9600000
Argentina 55053875 10000000 2606 2024-05-02 10000000 10000000
France 58239472 10000000 3201 2024-05-02 10000000 10000000
Egypt 598833157 26000000 2342 2024-05-02 20000000 20000000
Egypt 598833157 26000000 105046 2024-03-03 30000000 26000000
Egypt 598833157 26000000 141676 2024-04-02 7200000 7200000
Cambodia 600526694 10000000 3306 2024-05-02 10000000 10000000
Turkey 603732558 10000000 2830 2024-05-02 10000000 10000000
Turkey 603732558 10000000 68901 2024-02-04 200000 200000
Mexico 613539683 10000000 3222 2024-05-02 10000000 10000000
South Africa 645067822 10000000 3377 2024-05-02 10000000 10000000
Malaysia 670405651 10000000 3136 2024-05-02 10000000 10000000
Philippines 829374517 10000000 2900 2024-05-02 10000000 10000000
Canada 897852173 10000000 3037 2024-05-02 20000000 10000000
Singapore 903750383 9800000 2743 2024-05-02 10000000 9800000
United Arab Emirates 904448505 10000000 3170 2024-05-02 10000000 10000000
Laos 962880062 10000000 3000 2024-05-02 10000000 10000000
4

Please check the moving aggregation node configuration

Find proper way to find the cumulative Total

5

Hi @yogesh_nawale

The MA is correct, the Java Snippet is correct. But that’s not enough. The thing is, all of them have to be based on user_name column.

Do you have any idea for that?