Sum up the values of dynamic incoming columns

[Brayndasilva]

Hi,

I am using the CrossTab tool to process a bunch of data to one group per row. Let's take invoices as an example here. I have multiple rows containing information on how my invoices are built up. The number of rows to an invoice vary. These translate to the unique attributes of an invoice, but also other values that I don't need to take in account.

The result of my CrossTab tool looks something like:

What would be the most efficient way to sum up all 'attr_' values dynamically per invoice? Some invoices may have 4 attributes that built up the total, others 10 or more. 

[afv2688]

Hello @Brayndasilva ,

Do you mean something like this?

Regards

[Brayndasilva]

Hey @afv2688,

Thanks for your reply!

That looks good, but for my use case I would need a total column per invoice/row. I'll add that detail to my original post

[afv2688 · Accepted solution]

Oh alright, sorry my mistake 🙂

Here I got you an example and what I think would be the easiest way to remove values to not be taken into account for the calculations.

Regards

[Brayndasilva]

Thanks, your solution works fine!

With the help of the R tool I was able to achieve the same with a few lines of code:

library(dplyr)
data <- read.Alteryx("#1", mode="data.frame") %>%
  select('invoice_n',contains("attr_"))

data[is.na(data)] <- 0
data$total <- rowSums(data[2:ncol(data)])

write.Alteryx(data, 1)