I was wondering if someone could help me out with doing a MIN across multiple columns, but not coming back with values that are 0.
The other question is that if I use Min/Max how I can determine the source of where the value is coming from?
Thanks,
Justin
Solved! Go to Solution.
Can you make up some sample data to represent your situation, what you have for input, and what you expect for output? Thank you!
Hi @jmarleigh
Here's one way to approach it.
1. Transpose data to get all the column names in one column and values in one column.
2. Retain only values > 0.
3. Sort data by the record ID, then by the value column.
4. Use Summarize to take first (minimum due to the sort) and last (maximum due to the sort).
Does this get the result you are looking for?
Let me know if something isn't clear or there is something I missed.
Sure, below is what I'm expecting to happen, I just want to know how to get the Min of the 3 values (Column 6), but not the one with a 0 in it, and also have another column tell me the header of where the min came from; in this case it would be value 2 (Column 7). Right now when I drop a formula into the workflow and use the min function it will give me 0 from Value 3, instead of 4 from Value 2.
Store | SKU | Value 1 | Value 2 | Value 3 | Min | Source of Min |
8521 | 1000100103 | 5 | 4 | 0 | 4 | Value 2 |
I was thinking something like a Multi Field Formula would work to change all the 0s to NULL and then just run the MIN formula right after would work, but I don't know how to change all the 0s to NULL values in Alteryx. Same goes for showing the source of the min value. I've done this in Excel, but I can't translate it to Alteryx.
The Multi-Field Formula will turn 0 to NULL using this formula:
IF [_CurrentField_] > 0 THEN [_CurrentField_]
ELSE NULL()
ENDIF
Then you can use the MIN function for the fields. But it won't give you the column.
The workflow above with give you both the MIN and the column, plus the MAX and the column for that.
Philip,
Thanks for the formula. I downloaded your previously posted workflow, but was unable to open it because you're using a newer version of Alteryx than I am so I received an error.
Is it possible for you to just walk me through what's required to find the source of the min/max after the 0s are filtered out?
Works perfectly.
Thank you Joe.