Max or Min value of multiple columns of a case table

Hello,

I have a case table with quite a few value fields/columns. I would like to put the maximum value of these fields as into one OLAP table column per cases.

could you please share how it should work?

Thanks,
Denny

Hi,

please create an OLAP table and add the identifier of your case table as dimension (e.g. the case key). Then you can create several KPIs for every column of your case table you want the max or min value. The Formula would be

MAX(“YOUR *CASETABLE”.”COLUMN”)

-* For example for P2P you would state

MAX(“EKPO”.” NETWR”).

Hope this helps!

Best regards,

Justin

Hi Justin,

Thanks!

MAX() can return the maxiumum value of ONE column
However my situation is that I have case table like this

Detailedtable:

CASEKEY ValueKey1 Valuekey2 ValueKey3 …
1 1.0 2.0 3.0
2 3.0 2.0 1.0
3 1.0 4.0 3.0

the result I expect would be:

CASEKEY MAXValue
1 3.0
2 3.0
3 4.0

i.e I would maximum value of multiple columns per case.

Best regards,
Denny

Hi Denny,

thanks for your reply! In this case you will need a manual workaround as there is no function to calculate that in an OLAP.

You need to compare the columns manually in the code. Here is an example for your 3 columns to compare:

CASE WHEN COLUMN1 > COLUMN2 THEN (CASE WHEN COLUMN1 > COLUMN3 THEN COLUMN1 ELSE COLUMN3 END)

ELSE (CASE WHEN COLUMN3 > COLUMN2 THEN COLUMN3 ELSE COLUMN2" END)

END

With this workaround you will get the highest value out of the values of the three columns.

Let us know if that works!

Best regards,

Justin

Hi Justin,

Thank you! that works!
Would this kind of features added to the future IBC release?

Best regards,
Denny

Hi Denny,

we already have such functions, and you can already use them. Like in SQL, the’re called GREATEST() and LEAST(): You just pass all the columns as arguments (e.g. GREATEST("Table"."Column1", "Table"."Column2", "Table"."Column3")), and it will return the largest (GREATEST) or smallest (LEAST) value in each row.

Those functions were added very recently to the IBC. The documentation on them will be added soon, but they are already available to use.

For on-prem CPM4 users, those will be available with the next 4.7 release.

Best
David

2 Likes

Fantastic! this is exactly what I expected.