topic Re: find max and min value with dates in QlikView

find max and min value with dates

Anonymous — Sat, 16 Nov 2024 04:54:43 GMT

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

Re: find max and min value with dates

johann_bauer — Fri, 21 Dec 2018 07:48:34 GMT

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.

Johann

Re: find max and min value with dates

Anonymous — Fri, 21 Dec 2018 07:53:56 GMT

Yes need it using load script

Re: find max and min value with dates

johann_bauer — Fri, 21 Dec 2018 08:07:33 GMT

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

Re: find max and min value with dates

Anonymous — Fri, 21 Dec 2018 08:12:20 GMT

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.