find max and min value with dates

[Anonymous]

Experts,

I have this table at load script level

product	subproduct	type	bucket	NewMoB	vintage	vintageYrMon	NewMOBPercent
CD	NPB	RFP	1	1	01-01-2015	24169	2.74%
CD	NPB	RFP	1	1	01-02-2015	24170	2.03%
CD	NPB	RFP	1	1	01-02-2016	24182	1.40%
CD	NPB	RFP	1	1	01-06-2017	24198	1.20%
CD	NPB	RFP	1	1	01-07-2017	24199	1.40%

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

product	subproduct	type	bucket	NewMoB	vintage	vintageYrMon	MinValue	MinDate	MaxValue	MaxDate	AvgValue
CD	NPB	RFP	1	1	01-01-2015	24169	1.20%	01-06-2017	2.74%	01-01-2015	1.76%

Thank You in advance !! 🙂

[johann_bauer]

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]

Yes need it using load script

[johann_bauer]

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]

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.