community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Finding the minimum value across multiple columns

Atom

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 1Col 2Col 3Col 4Col 5Col 6Col 7Col 8Col 9Col 10Minimum Value
A1  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     333

 

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! 

Alteryx Certified Partner

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.

Aurora

Hi @jakechan 

 

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

 

 W.png

 

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

 

r.png

 

Dan

Meteor

Hi Jakechan,

 

Here is another way to achieve your results.

 

Jakechan_query.jpg

Aurora

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

Meteor

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.

Alteryx Certified Partner

@danilang You should always follow my advice!

Aurora

@LordNeilLord 

 

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

 

Dan

Atom

Thank you for a quick response! Worked perfectly 

Highlighted
Atom

Thank you for this! Just what I needed 

Labels