2 Replies Latest reply: Oct 10, 2012 9:38 AM by 965732 RSS

    find MIN, MAX of multiple rows from multiple columns

    965732
      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
          Srini VEERAVALLI
          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
            965732
            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)

            CASE WHEN filter ("- Terminal Task Measures"."Task Reported DateTime (Local)" using "Task Detail"."Task Code" = 'bgn-crm-insp-ob') > filter ("- Terminal Task Measures"."Task Reported DateTime (Local)" using "Task Detail"."Task Code" = 'report-bo-ob') THEN filter ("- Terminal Task Measures"."Task Reported DateTime (Local)" using "Task Detail"."Task Code" = 'bgn-crm-insp-ob')

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

            WHEN filter ("- Terminal Task Measures"."Task Reported DateTime (Local)" using "Task Detail"."Task Code" = 'report-bo-ob') IS NULL THEN filter ("- Terminal Task Measures"."Task Reported DateTime (Local)" using "Task Detail"."Task Code" = 'bgn-crm-insp-ob')

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

            ELSE filter ("- Terminal Task Measures"."Task Reported DateTime (Local)" using "Task Detail"."Task Code" = 'report-bo-ob') END



            Hopefully this works, I'll give feedback if it does, or if you have any further suggestions please submit, again, THANK YOU SO MUCH ANYWAYS!