Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
rathodniky
Creator
Creator

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 !! 🙂

Labels (4)
4 Replies
johann_bauer
Partner
Partner

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

Yes need it using load script

johann_bauer
Partner
Partner

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

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.