Hey,
I’m trying to do the exact same thing an the sumifs function in excel but I don’t really know how to.
I have found that it does work like that:
Sequence of two “Math Formula” nodes:
First one: Multiply the two columns using the expression Col1*Col2 and
Append a new column.
Second one: Use COL_SUM on the new column.
Is there any example for this nodes? I need some help please
Hi,
to use math formula node you need to convert the values that you want to calculate to decimal using a “String to Decimal Node” then you can use them in “Math Formula Node”.
For the second one you need a group by and choose groupby SUM
But the 2 columns are in two different sheets. How can I use the Maths Formula node in this case?
for two tables you need a joiner to merge them and then do your calculation
The joiner joins the two columns in just one single column, well I need to have two columns for my calculations.
the joiner joins also many columns based on one or many columns which have similar values.
Can you upload your problem ? I think i can solve it
Here is my problem
Table 1: ( Sheet 1)
| Product | Location | Number |
|---|---|---|
| 543 | A | 2 |
| 246 | A | 3 |
| 754 | A | 5 |
| 3534 | A | 2 |
| 654 | B | 3 |
| 634 | B | 5 |
| 7665 | B | 1 |
| 235 | B | 0 |
| 876 | B | 4 |
| 54 | B | 1 |
| 56 | A | 7 |
| 234 | A | 0 |
| 7865 | A | 32 |
| 1323 | A | 1 |
Table 2: (Sheet 2)
| Product1 | Number A(Resulting column) |
|---|---|
| 543 | 2 |
| 3534 | 2 |
| 654 | 0 |
| 634 | 0 |
| 56 | 7 |
| 7865 | 32 |
| 1323 | 1 |
The function of my resulting column :
SUMIFS(Sheet1!C:C,Sheet1!A:A,Sheet2!A3,Sheet1!B:B,“A”)
How to do the exact same thing in Knime?
Thank you
Does it help ?
No, it doesnt work.
The number isn’t an input for the second table
It seems you need to add a Row Filter for table 1 for second column equal to A. Then the Enigma2018 solution will work.
Hi there!
KNIME offers multiple ways to deal with this. I have created 2 solutions. Using Joiner with Row Filter node as @izaychik63 said or Refrence Row Filter node.
In both cases you only need GroupBy node if there is multiple same products in your Product Data. Otherwise you do not have anything to sum and you do not need GroupBy node.
Here is workflow attached.
2019_03_06_SUMIFS Excel function.knwf (26.3 KB)
Regarding this solution I’m not sure how it should work. I know it is from Excel to KNIME book. If modification is needed I will report it.
If any question feel free to ask.
Br,
Ivan
Hi there!
I got it. This instructions:
are actually for SUMPRODUCT() Excel function.
For SUMIF() instruction is following:
Sequence of two “Math Formula” nodes:
First one: Append new column with if(criteria, column to sum ,0)
Second one: Use COL_SUM on the new column
Considering you are using SUMIFS() function above instructions do not apply.
Br,
Ivan
Okay, thank you.
I’ll try this solution and get back to you
