topic Re: Filter table rows by min/max dates in QlikView

Filter table rows by min/max dates

jrdunson — Thu, 31 Mar 2016 15:27:30 GMT

Just am ing to show rows for Min and Max dates... plus specific columns for those dates (e.g. comments, for instance):

I have some rows… with Due Dates and Complete Dates (here is a sample...see attached xlsx)... and comments

AK	ID	CorrID	CorrName	DueDate	CompletedDate	CompletedFlag	Comments
11053868	58695	156998	St	NULL	7/10/2001	Y	Pho
11077072	58695	156998	St	NULL	7/6/1999	Y	Let
11105388	58695	156998	St	9/21/2012	9/20/2012	Y	Ema
11105902	58695	156998	St	NULL	10/8/2012	Y	Mrs

This is what I want to show

I've tried to use a number of methods, max(), firstsortedvalue(), aggr, etc. - but don't know how to attach the comments

ID	CorrID	date(min( {$<[CompletedFlag]={'N'}>} DueDate))	max(CompletedDate)
58695	156998	7/1/2013	10/12/2015
58695	159827	-	12/11/2015
58695	743786	-	10/10/2014
58695	875444	12/5/2014	7/30/2015
58695	500505221	-	7/30/2015
58695	700103835	-	3/17/2013

Re: Filter table rows by min/max dates

sunny_talwar — Thu, 31 Mar 2016 15:37:55 GMT

See the attached, is this what you want?

Re: Filter table rows by min/max dates

jrdunson — Thu, 31 Mar 2016 15:50:07 GMT

Sunny,

thanks... that was quick.. and seems to work... Thank you so much

Jarrell

Re: Filter table rows by min/max dates

jrdunson — Thu, 31 Mar 2016 17:44:00 GMT

SunnyT

Can I ask a follow up...

In the example above, we used one table, where the ID and CorrID were selected as dimensions...so the FirstSortedValue solution works fine..

In my data model (which is difficult to duplicate in this forum), however, I have a number of tables associated via an ID field ... . can I reproduce the same results in another table (where the CorrID isn't selected)?

For example, in the attached qvw, can I create a table...showing ID, Rating ID (from the ratings table), but then reproduce the same results from the tasks table? .. [something like where Rating_ID = Corr_ID]

Any help would be appreciated,

Thanks

Re: Filter table rows by min/max dates

sunny_talwar — Thu, 31 Mar 2016 17:50:47 GMT

May be like this?

Re: Filter table rows by min/max dates

jrdunson — Thu, 31 Mar 2016 17:58:47 GMT

Sunny,

Thanks, you are a great help... I think this will work... in my larger data model....and will take a look... so thanks,

Jarrell

Re: Filter table rows by min/max dates

sunny_talwar — Thu, 31 Mar 2016 18:00:40 GMT

Just a quick note that this is not good for performance as your are creating a Cartesian product between Rating_ID and CorrID.