I’m trying to make a simple MIN() column, should return the minimum of the three colum prices, but running into problems when one or more columns are missing value - tips?
Hey @jorgenandreassen,
You could modify the MIN() function and include an IF statement so that if the cell is empty, then make it a very large number, so that the MIN() ignores it:
min(min(if(isempty([Onninen]),9999999999,[Onninen]),if(isempty([Solar]),9999999999,[Solar])),if(isempty([Sonepar]),9999999999,[Sonepar]))
It would look like this:
I attach a sample project:
MIN Ignore empty cell.morph (3.1 KB)
Regards!
2 Likes
Hi, jorgenandreassen;
Another solution would be to unpivot the data, use the Aggregate action’s “Min” setting, which ignores empty cells, then merge the mins back onto the rows. No formulas are required.
My concern with setting static values is if there’s a chance that value could possibly appear in the column somewhere (as a valid value).
So, basically;
- Derive the table twice. (You’ll need one for the mins, and one to carry through the original data.)
- Unpivot to create a “tall” dataset. Check “Varenr” under “Unpivot all columns except these” (as these appear to be a unique IDs for the row).
- Aggregate the Data (column of numbers) column using the Min setting (empty cells are ignored using this).
- Merge another table - Merge the calc’d Min values back into the second Derived table (original source data, carried through) using the ID field.
When dealing with row-level calc’s (aggregations), unpivoting, aggregating, and pivoting can have advantages over using in-line formulas.
Example attached (workflow with dummy data file). Note, in my example (using dummy data), I Enumerated Rows to create unique row ID #s. You wouldn’t need to do that as you already have row IDs you can use. At the end, I removed the RowNo column and Renamed the column with the min values as “MinValue”.
CraigT
Min without Empty EM workflow.zip (2.0 KB)
2 Likes