Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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 - Contributor III
Partner - Contributor III

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

 

Anonymous
Not applicable
Author

Yes need it using load script

johann_bauer
Partner - Contributor III
Partner - Contributor III

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

Anonymous
Not applicable
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.