EG - Using query builder, how to get row with latest date value?

ncsthbell · Posted 03-07-2012 03:06 PM

The data I am retrieving in the EG project has multiple rows for a 'Vendor# & 'Invoice#'. Each of these rows has a column with a date field 'Last_updated'. I need to be able to retrieve the row for this Vendor#/Invoice# with the latest date in the 'Last_updated'. I am trying to build this using EG query builder but I do not see a function that I can use to accomplish this. Any suggestions on how to accomplish this in EG?

THanks

	
		OSZAR »

Technolero · Posted 03-07-2012 03:29 PM

On the Select Data tab, choose Vendor# and Invoice# and Last_updated. In the Last_updated row, select MAX from the Summary column. EG will automatically do summarization of the latest date by Vendor# and Invoice#.

	
		OSZAR »

ncsthbell · Posted 03-07-2012 03:51 PM

This works if I only include the Vendor & Invoice number. I have many more columns of data that I need, the Vendor & Invoice number are just keys that I would be looking to group by. I put the other columns on my select tab and since it is looking at each column on the select, I did not get what I need. I should have mentioned that I have other data I need on the select tab and the 'grouping' used for finding the lastest dated row would be by vendor# and invoice#.

	
		OSZAR »

Technolero · Posted 03-07-2012 03:59 PM

I would run the query that I described above, then use that output to join back to your original data set to select the rows that match Vendor#, Invoice#, and Last_Updated.

	
		OSZAR »

ncsthbell · Posted 03-07-2012 04:00 PM

Thanks, I'll try that.

	
		OSZAR »