Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
rathodniky
Contributor

find max and min value with dates

Experts,

I have this table at load script level

 

productsubproducttypebucketNewMoBvintagevintageYrMonNewMOBPercent
CDNPBRFP1101-01-2015241692.74%
CDNPBRFP1101-02-2015241702.03%
CDNPBRFP1101-02-2016241821.40%
CDNPBRFP1101-06-2017241981.20%
CDNPBRFP1101-07-2017241991.40%

 

I need the following output where the output has max and min values with its dates

productsubproducttypebucketNewMoBvintagevintageYrMonMinValueMinDateMaxValueMaxDateAvgValue
CDNPBRFP1101-01-2015241691.20%01-06-20172.74%01-01-20151.76%

 

Thank You in advance !! 🙂

4 Replies
Partner
Partner

Re: find max and min value with dates

Hello,

Do you want to achive the result in the loading script or in application level?

If you want to achive in loading script, you will need to use some aggregations with a group by clause and sone interrecord functions like peek/previous in your loading Script. Unfurtunately iam in a subway and cant test it out.

Kr

Johann

 

rathodniky
Contributor

Re: find max and min value with dates

Yes need it using load script

Partner
Partner

Re: find max and min value with dates

in this case a Script like this would do the trick. You now just have to make sure about your date format and your percentage Value

 

Load
product,
subproduct,
type,
bucket,
NewMoB,
min(vintage) as vintage,
min(vinatgeYrMon) as vinatgeYrMon,
min(NewMOBPercent) as MinValue,
max(NewMOBPercent) as MaxValue,
avg(NewMOBPercent) as AvgValue
group by
product,
subproduct,
type,
bucket,
NewMoB

from...

rathodniky
Contributor

Re: find max and min value with dates

directly using Min on yrmon and vindate date would pick min/max by itself but I want the date and yrmon of the min and max value.