How to find min and max value

[krish2459]

Hi,

How to find min and max value in entire data set.

Script:

Risk_Master:
LOAD

site_id as "Site ID",
site_id as "Site ID_Exists",
if(IsNull(mpl_total),'-',mpl_total) as "MPL total_1",

'1' as flag_ID,

(if(IsNull(mpl_total),'-',log(mpl_total+1)) - min(mpl_total))/(max(mpl_total)-min(mpl_total)*(5-1)+1) as "Maximum Potential Loss_1"

FROM [lib:/072020.xlsx]
(ooxml, embedded labels, table is report1596493779246)group by site_id,mpl_total;

But min max is working on low level.

Here I need Min and max value of entire dataset.

For eample in the below Image "725016119" is the lowest vale which need toshow in all rows as min.

Thanks..

[JosephMorales · Accepted solution]

Hi @krish2459 

You can do the following, create 2 variables that contain the minimum the maximum of the data set, and then use these variables in your calculation in the following way:

TEMP:
LOAD
MIN(mpl_total)	AS MIN,
MAX(mpl_total)	AS MAX
FROM [lib:/072020.xlsx]
(ooxml, embedded labels, table is report1596493779246);


LET vMaxValue=peek('MAX',0,'TEMP');
LET vMinValue=peek('MIN',0,'TEMP');

DROP TABLE TEMP;

Risk_Master:
LOAD
site_id as "Site ID",
site_id as "Site ID_Exists",
if(IsNull(mpl_total),'-',mpl_total) as "MPL total_1",
'1' as flag_ID,
(if(IsNull(mpl_total),'-',log(mpl_total+1)) - $(vMinValue))/($(vMaxValue)-$(vMinValue)*(5-1)+1) as "Maximum Potential Loss_1"
FROM [lib:/072020.xlsx]
(ooxml, embedded labels, table is report1596493779246)
group by site_id,mpl_total;

Regards

Joseph Morales

[krish2459]

Thanks Joseph. It woking.