How to apply formula to entire row (without manually specifying each existing field)

Hello everyone, I have a beginners question on performing quick calculations on a whole row.

Let's say I have a large table, where the rows represent products, and the columns are different customers (let's say the data is number of items sold per customer).

Products	Customer 1	Customer 2	Customer 3	Customer 4	...	Customer N
A	xxx	xxx	xxx	xxx		xxx
B	xxx	xxx	xxx	xxx		xxx
...
M	xxx	xxx	xxx	xxx		xxx

What I would like to do is to add columns "median", "max", "min", "percentile" with the respective numbers computed for the entire row (except for the first column, which I have deselected with a select tool for my purpose).

When using the formula tool, I can use "Max(v0, v1)", and replacing the v0,v1 with all the columns that I have. Is there a command I can type in that the tool automatically selects all columns? This would not only be more convenient, but I am also using differently sized inputs and I would be able to run the workflow without changing the formula expression.

The only workaround I was able to think about is to transpose the whole table with crosstab and transpose, and then instead of using the Formula tool, use Summarize to do all the calculations, and then transpose back. Is there a better way for doing this?

PS: Is there a simple expression for the Formula tool to calculate e.g. the 25th percentile? Or would that require a workflow, e.g. with ranking the numbers etc.?

Thank you so much for your help!

Best Practices

Accepted answers

MarqueeCrew

In your example, we have taken a static set of fields into a TRANSPOSE tool first. Given no key or a fixed key (can be multiple fields), it takes the key and adds a NAME field and a VALUE field to the record. The name represents the column (data element) name and the value represents the contents of the row for that given header. If you keep the key and change the columns, the output going into the SUMMARIZE tool will remain constant.

On many tools that have a SELECT function included with them, there is a checkbox for "Dynamic or Unknown Fields" that is checked by default. As long as that box is checked in your TRANSPOSE tool, you should be good with the addition of elements.

Thanks,

Mark

All comments

MarqueeCrew

Your transpose method is what the doctor ordered. I'd use the tile tool to calculate your percentile too.

You're on the right path. Check out the tile tool in help.

RodL

Check out the Percentile function in the Summarize tool. Since you will have all of your data transposed anyway, you can group by the column names and get the percentile you need. Note that you enter the percentile you want at the bottom of the Configuration panel...it's easy to miss. Smiley Wink

MarqueeCrew

Nice tip @RodL

tl92

Thank you for your help so far!

I have a follow-up question: When using the Summarize tool, I can select all fields at the "Fields"-box. As I understand it, the tool reads out the input file structure and adds actions for all fields. However, what happens if I later change the input file to one with more fields? If I do not change anything in the Summarize tool, then I will only perform the calculations for the fields that have been specified with the previous file. Is there a method to prevent this within Summarize?

MarqueeCrew

Thanks,

Mark

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703