Filter table rows by min/max dates

[jrdunson]

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

[sunny_talwar · Accepted solution]

See the attached, is this what you want?

[jrdunson]

Sunny,

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

Jarrell

[jrdunson]

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

[sunny_talwar]

May be like this?

[jrdunson]

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

[sunny_talwar]

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