Convert decimal numbers to the sum of 100

Hi KNIME experts,

I have come across this issue today where a project contains more than one row but each row has a credit percentage of 100 as shown below and counter column is how many rows does this project have in this workflow.

image

I had to divide this credit percentage by Counter and this is what I got after the math column which is credit percentage is 33.333.

image

However the sum of this credit percentage column for a particular project must be equal to 100. This is the place where issue comes that our system accepts only up to two decimal points and when I do this I get the sum of the credit percentage as 99.99 but I need this to be 100.

image

What can I do to overcome this issue, could anyone help me please with this issue?

Thanks in advance.

Please note I have attached the excel file if in case needed.

Projectsss2.xlsx (9.2 KB)

Not sure how your upstream calculations are set up. In the Math Formula node you can use “Convert to Int” or use the Round Double node.

3 Likes

I used both then I get 33 only when I convert this into int, and when I use round to Double then I get 99.99 the calculation of all 3 rows but the sum must be 100 which are 33,33, and 34 so it sum up to 100.

1 Like

Don’t convert the intermediate calculations. Convert the summed total (99.999)

3 Likes

Yes it still gives 33.33 for all rows. i need a way to convert this into 100 but 33.33 * 3 sum up to 99.99

Hello @psycho

I’m not sure what the business rules are. But, as a final patch you can add at the end a ‘Math Formula’ node, replacing $credit percentage$ column, the code can be something like this:

if($credit percentage$ == 100
	, 100
	, $credit percentage$ + (100 - mod($credit percentage$, 100))
)

or

if($credit percentage$ == 99.99
	, 100
	, $credit percentage$
)

BR

Is the Excel file you posted the actual input? If so, you can’t do any math calculations in Knime since the “credit percentage” column is a string. See the workflow below for how to handle the values as strings. The bottom branch converts to numbers which the loses intermediate “headers”.

but I have 3 rows with 33.33 for a project so I need to get the sum of these 3 rows and if it is not equal to 100 then add 1 to one row out of three rows. This issue is there for all types projects which have odd number of rows.

This is different from your first description. Could you post the input data and desired outcome based on this new information? Also are you doing some of the calculations in Excel or do you want to do everything in Knime?

1 Like

shall i post a new whole new post deleting this?

Don’t start a new post on the same subject. Just post the new information here.

Hi rfeigel,

the first table is what we have now, and second table shows the percentage after divide the percentage by the number of rows which is the counter column. Finally, the third table shows the sum of percentages which must be 100 but it is not for some projects such as project 2 and 3. Is it clear now?

Please post the data rather than a screenshot. Also this is different than your first post. Originally you wanted 99.99 rounded to 100. Is that what you still want?

1 Like

This is not sounds as technical task. On organizational level you have to review all 99.99
project and mark finished one as closed or done 100%.

The problem is that column F is rounded to the second decimal point. You can somewhat clean it up by recalculating those percentages (Column F) with a much higher decimal count for additional accuracy. I always keep a high degree of decimal accuracy on percentages and then only round them off to the side or at the end for report building or views. The differences will be minuscule at the end, so the totals will also clean up when you round to 2 decimal points for presentation purposes.

2 Likes