This discussion is archived
2 Replies Latest reply: Oct 10, 2012 7:38 AM by 965732

find MIN, MAX of multiple rows from multiple columns

Currently Being Moderated
Hello,

I need to figure out how to pull the MIN/MAX of multiple rows from multiple columns into one column. Even if some are NULL/blank.

For Example: (C: Column, R: Row, N - NULL/Blank)

C:____1____2____3____ 4____Max
R:____20___22___13____4____*22*
R:____N____N____32____14___*32*
R:____N____12____N____N____*12*

That is, it always gives a value for MIN/MAX unless there are NO values in all the rows of the columns.

So if there is one value, it will select that for the MIN/MAX, as it's the smallest/biggest since there is nothing to compare it to.

Here is my current code:

CASE WHEN COLUMN 1 < COLUMN 2 THEN COLUMN 2 ELSE COLUMN 1 END
• 1. Re: find MIN, MAX of multiple rows from multiple columns
Currently Being Moderated
For max and min use the below expression something like
MAX(GREATEST(DEPARTMENT_ID,MANAGER_ID))
MIN(GREATEST(DEPARTMENT_ID,MANAGER_ID))
you can have N number of columns inside the function.

Call these functions using evaluate in BI.

Pls mark as correct/helpful if helps
• 2. Re: find MIN, MAX of multiple rows from multiple columns
Currently Being Moderated
Hi Thank you for your feedback, unfortunately, I just found out that EVALUATE Function is disabled in our environment for security reasons, so the only other way I've discovered is this:

The problem is that none of the conditions in the case statement are met--so the column is set to null. You can add a WHEN statement (section 2 below) to catch the nulls. There are five cases to consider:

Case 1: begin insp > bad order
Case 2: begin insp < bad order
Case 3: bad order only (begin insp is NULL)
Case 4: begin insp only (bad order is NULL)
Case 5: both begin insp is NULL and bad order is NULL

1) If bgn-crm-insp-ob > report-bo-ob then bgn-crm-insp-ob
(Case 1)

2) If report-bo-ob is NULL then bgn-crm-insp-ob
(Case 4, 5) for case 5, you will get NULL

3) Else report-bo-ob
(Cases 2, 3)