Finding the minimum value across multiple columns

Hello

I'm new to alteryx and I'm hoping there's a simple answer to this

I want to find the minimum value across a row, for example

	Col 1	Col 2	Col 3	Col 4	Col 5	Col 6	Col 7	Col 8	Col 9	Col 10	Minimum Value
A	1			3		4			4		1
B			22								22
C						3		2			2
D				22					3		3
E								2			2
F				3			3				3
G						2			2		2
H				3						33	3

And I want to create that last minimum value column in alteryx.

One way I could do this is with a formula MIN(Col 1 + Col 2 etc..), however I have too many columns to individually type out. Is there another way I can do this? Or more broadly, how can I write a formula that say "from Col X to Col Y". The excel version would be MIN(Col 1:Col 10)

Thank you!

Preparation

Accepted answers

LordNeilLord

Hey @jakechan

The easiest way to do this will be to transpose your data grouping on your ID (A,B etc) then use summarize (group by ID, min of value)

Then you can join this result back to your original table.

All comments

LordNeilLord

Hey @jakechan

The easiest way to do this will be to transpose your data grouping on your ID (A,B etc) then use summarize (group by ID, min of value)

Then you can join this result back to your original table.

danilang

Hi @jakechan

Here's a workflow to illustrate what @LordNeilLord described in his response.

Start by cleaning the data and adding a RecordID in case the Label column in your real data ins't unique. Transpose the value columns so they end up in a pair of columns, Name which holds the column name and Value which holds the values. The Summarize tool finds the minimum value for each RecordID. Join this column back to your main data on RecordID and you have your results

Dan

MinValue.yxmd

Dazzerman

Hi Jakechan,

Here is another way to achieve your results.

Jakechan_query.yxmd

danilang

Hi @Dazzerman

You have a good solution, but in general it's better to join back to the original data, if you can, as opposed to using a crosstab to rebuild it.

The crosstab tool suffers from 2 minor drawbacks

1. All non-alpha numeric characters in the columns are replaced with under scores. You can see this in your solution where "Col X" becomes "Col_X" for all your output columns. You can use a dynamic rename to replace "_" with spaces in this case but if your column names have a combination of special characters, i.e. "Month / Year" which is converted to "Month___Year", fixing the problem is harder.

2. Columns in the output are reordered alphabetically. You don't see this in the sample data because the columns are already sorted but it's a major pain in real world data. To get around this issue you need to create a numeric column order field and use this as the Column header field in the Cross tab and then rename based on the numeric name

In this example where you're adding a new column to an existing table it's much easier and more efficient to follow @LordNeilLord's advice and join back to your original table

Dan

jakechan

Thank you for a quick response! Worked perfectly

jakechan

Thank you for this! Just what I needed

Soraju

Thank you for the solution. It worked for my workflow as well. I am needing to go a step ahead here and display the column header too. For example record ID 1 has a minimum value of 1 and thats coming from col 1. Is there a way I could display Col 1 in my result too. ?

something like this :

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703