How to apportion based on specific fields

[Barbo87]

I have the “Declaration” field that repeats “x” times according to the “invoice number”, but I have a total amount “exempt” and “taxed” by the “declaration” number. I need to divide that total “taxed” and “Exempt” amount proportionally, by the “x” times of each invoice according to the “Declaration” number. Thank you for your help or support.

[wkhan]

Hi @Barbo87,

Welcome to the forum. If I understand correctly you want to sum the total Tax and Exemption by Declaration Number. You can do this with the Group By or a GroupLoop and use the Math Formula – KNIME Hub to divide.

Hope that helps

[Barbo87]

Hi @wkhan

Thank you for the welcome and the answer. But no, the idea is to divide the exempt and taxed total proportionally in each of the invoices that make up the Declaration Number.
Because I have a single declaration number, but it is made up of “x” number of invoices.

Again, thank you for the help.

[takbb]

Hi @Barbo87 ,

How do you see this data presented to KNIME? Would you be able to upload a sample spreadsheet, as I’m wondering how the “merged” cells will appear.

I think the following is possibly similar to what @wkhan was suggesting.

On the face of it, I’m assuming that you can treat “Declaration” as an identifier for the specific value, so if you return a result table which is the count of each “Declaration” (using a groupby node with a count grouped by Declaration), this will then give you the factor by which each of the financial values should be divided.

I would look at populating the taxed and exempt values down across all rows, so where there is a grouping by Declaration the current total is repeated.

You then get the separate count of each declaration using the GroupBy node as mentioned.

Join that count back to the original data table, and divide each amount by the returned count.

That should work, but to some extent is does depend on the form your original data takes when it is supplied to KNIME

I’ve mocked up a demo here
apportion amounts.knwf (26.8 KB)

And welcome to the KNIME Community!