How do I create a column that sums the total of each row based on the invoice #, but leave the other rows blank. So it doesn’t repeat itself?
| Column 1 | Column 2 | Column 3 | Column 4 |
|---|---|---|---|
| Inv1 | $10 | $45 | |
| Inv1 | $15 | ||
| Inv1 | $20 | ||
| Inv4 | $2 | $9 | |
| Inv4 | $3 | ||
| Inv4 | $4 |
You can’t sum data with $ signs. They’re strings. Save your data as numbers.
Hey there,
in addition to what @rfeigel said - can you explain a little more (or even better provide an example) about what the desired output is?
Based on your current explanation I am unsure whether you’d like to see e.g. for your first row 55 in a new column and second row 15 or if you want to sum all cells that are in an Inv1 row (i.e. see a total of 90).
Regards,
Martin
Hi @vivianmpoon.
After converting Column2 to number, this is my aproach.
-
Generate totals.
Use a group by node to sum values on Column2 agregated by same value on Column1.
Rename the sum as Column3 -
Append Column3 generated on 1)
Use a join node to (left join) by column1, to append that Column3 result. -
Remove values if same invoice.
Use a column expression node to relace Column3 and suppress values if same invoice
$Column1$ = $Column1(-1)$ =>“”
Br
3 Likes
So when I do a groupby node to calculate the sum of each invoice amount, it gives me the totals. Then I use a joiner node to bring the total back to the original table, but the total sum of each line will keep repeating itself as per below.
How do I make it so that column 3 (the grand total of each invoice #) only repeats itself once?
| Column 1 | Column 2 | Column 3 | Column 4 |
|---|---|---|---|
| Inv1 | 10 | 45 | |
| Inv1 | 15 | 45 | |
| Inv1 | 20 | 45 | |
| Inv4 | 2 | 9 | |
| Inv4 | 3 | 9 | |
| Inv4 | 4 | 9 |
@hmfa solved your problem.
1 Like
If you are ok with download components, and want a quick way of finding the final row for each invoice, I have a component First and Last for Group which will do that.
You tell it which column identifies the “group” (in your case Column1 with the invoice number)
Then similar to @hmfa’s approach, a Rule Engine blanks the “other” row values.
Of course your output will have to be a string, because otherwise it cannot be set to blank. The best you could do for Integer is to have missing values instead of blanks , by leaving out the final line in my Rule Engine example.
3 Likes
1 Like
Hello @vivianmpoon
I couldn’t avoid a take to this challenge. @hmfa 's workflow is the simplest approach to it; I’ve tested to process the cumulative computation with ‘Moving Aggregator’ + ‘Column Expressions’ node.
It may look too complex for a single column; but it escalates quite easy for complex logistics , as you see in this post. This approach avoids loops and joiners increasing in efficiency.
BR 
3 Likes