Convert complex Excel function into KNIME
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.