Replace a value (row) based on percentages from another table (multiple rows)

Dear KNIMErs,

I am struggling with the following problem:

On Table 1, each Account has a total Amount by a specific date. I would like to replace this total amount based on the percentage included on Table 2 (Table 2 is different every month).
The result should be like Table 3 (Account 710240).

My main problem (at least one of them :grinning:) is to create the relation between Table 1 and Table 2.
Any thoughts how to tackle this? Your guidance is very well appreciated.

Thank you in advance

Based on what you’ve shown here, the logic for creating Table 3 is not clear to me.

How and why did Department and Project get filled in for accounts 211430 and 351800?

Can you explain further?

Yes, you’re right elsamuel. Accounts 211430 and 351800 in Table 3 shouldn’t have Department and Project in the example. Better, they should have also been filled in Table 1 previously. So please, focus on account 710240

The rationale behind this is that some of the accounts already have Department and Project and are not dependent from percentages of Table 2. The ones who are dependent, must be replaced proportionally by Table 2.

Hope this helps for the solution. :slight_smile:

Hello @migueldevezas ,

try this:

If you don’t want to show account IDs without percentages in the last table, open the joiner node settings and keep only “inner join”.

Have a nice day,
Raffaello

3 Likes

Hi Raffello,

And thank you so much! This might be the solution! :smiley:
Let me check with real data and i will come back after testing it.

1 Like

Hi Raffaello,

Sorry for late reply, but your solution really worked!
Thank you so much :facepunch:

2 Likes

Glad it worked! :slight_smile:

Have a nice day,
Raffaello

1 Like