Max and Min Value with Dates

[Anonymous]

Dear Experts,

I have this table

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 this output

product

subproduct

type

bucket

NewMoB

vintage

vintageYrMon

NewMOBPercent

MinValue

MinDate

MaxValue

MaxDate

AvgValue

CD

NPB

RFP

1

1

01-01-2015

24169

2.74%

1.20%

01-06-2017

2.74%

01-01-2015

1.76%

Trying a script in load editor but does not work

Thank you in advance 🙂

[Ashvi01]

Hi,

Maybe try something along this line. I have done something similar with a 13 month trend to find min and max value. I have tweak it a bit but might need further amendments to make it work to your scenario. if you have year and month selection you might have to exclude them to make it work

min({<Date={">=$(=addMonths(max(vintage),-1)) <= $(=max(vintage))"}>} aggr (sum({$<DateValue={">=$(=addMonths(max(vintage),-1)) <= $(=max(vintage))"}>} NewMOBpercent),Date))

[rubenmarin]

Hi, one easy way can be loading it different times to flag records using peek to check if there is the same key, and do a final join to have all together, ie:

tmpData:
LOAD *, product &'_'&subproduct... as keyField resident/from...;

tmpRecordToKeep:
LOAD *, If Peek(keyField )=keyField , 0 ,1) as flagKeep
resident tmpData order by keyField, vintage;

tmpMaxValue:
LOAD keyField, vintage as MaxDate, NewMOBPercent as MaxValue,
If Peek(keyField )=keyField , 0 ,1) as flagMax
resident tmpData order by keyField, vintage, NewMOBPercent desc;

tmpMinValue:
LOAD keyField, vintage as MinDate, NewMOBPercent as MinValue,
If Peek(keyField )=keyField , 0 ,1) as flagMin
resident tmpData order by keyField, vintage, NewMOBPercent;

tmpAvg:
LOAD keyField, Avg(NewMOBPercent) as AvgValue
resident tmpData group by keyField;

Final:
LOAD * resident tmpRecordToKeep where flagKeep =1;
join LOAD keyField, MaxDate, MaxValue resident tmpMaxValue where flagMax=1;
join LOAD keyField, MinDate, MinValue resident tmpMinValue where flagMin=1;
join LOAD keyField, AvgValue resident tmpAvg;

DROP tables tmp...;