Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
krish2459
Creator
Creator

How to find min and max value

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.

 

krish2459_0-1612376953207.png

Thanks..

Labels (1)
1 Solution

Accepted Solutions
JosephMorales
Partner - Contributor II
Partner - Contributor II

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

View solution in original post

2 Replies
JosephMorales
Partner - Contributor II
Partner - Contributor II

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
Creator
Creator
Author

Thanks Joseph. It woking.