Finding the minimum value across multiple columns

[jakechan]

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! 

[LordNeilLord · Accepted solution]

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

[Dazzerman]

Hi Jakechan,

Here is another way to achieve your results.

[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

[Dazzerman]

Hi @danilang,

Thank you very much for the feeback.

I have overcome the challenges with column order in the past.  It's certainly a valid point, and necessary to address in circumstances where it is a problem.  I didn't want to over-complicate my suggested solution in this case, and was just intending to highlight a different approach.

That is one of the great advantages of Alteryx, in that there is usually different ways of coming to the same result, or overcoming problems.  And this community (which wasn't around when I first started using Alteryx) is invaluable for users to improve and hone their skills to addressing real-world problems.

Very grateful to you for sharing your view.

[LordNeilLord]

@danilang You should always follow my advice!

[danilang]

@LordNeilLord 

I try to, but you set such a high standard.  Just look at your beard!

Dan

[jakechan]

Thank you for a quick response! Worked perfectly 

[jakechan]

Thank you for this! Just what I needed