topic How to find min and max value in QlikView

How to find min and max value

krish2459 — Sat, 16 Nov 2024 17:31:44 GMT

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..

Re: How to find min and max value

JosephMorales — Wed, 03 Feb 2021 19:04:58 GMT

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

Re: How to find min and max value

krish2459 — Fri, 05 Feb 2021 18:43:39 GMT

Thanks Joseph. It woking.