Load only the most updated record among multiple listed

[#1]

Hi everyone,

I have an issue that I am hoping I can get some guidance and help with.  Basically, I am trying to match up sales information against quotes and their listed dollar amount.  They are on separate tables.  My issue is that for any given sales deal, there could be multiple quotes with multiple dollar amounts.  I am only interested in pulling the single, most updated quote.

Example:

Table 1 (Sales deals)

Sales Deal #
123
234
345
456
567
678
789

Table 2 (Sales Quotes)

Quote #	Sales #	Date Quote Created	Dollar Amount
1111111	123	1/4/2012	456.00
1111112	123	5/3/2012	3324.00
1111113	234	3/5/2012	4534.00
1111114	234	12/4/2011	4443.00
1111115	234	7/5/2012	8988.00
1111116	345	5/4/2012	7788.00
1111117	345	5/7/2012	7650.00

Sales # is linking the two.  I've highlighted in red the lines of data I'm interested in capturing.

Help is appreciated!

[nagaiank]

You can get the highlighted rows using the following script:

load [Sales#],

     FirstSortedValue([Date Quote Created],-[Date Quote Created]) as [Last Quote Date],

     FirstSortedValue([Quote #],-[Date Quote Created]) as [Last Quote],

     FirstSortedValue([Dollar Amount],-[Date Quote Created]) as [Last Dollar Amount]

     Resident [Table 2 Sales Quotes]

     Group By [Sales#];

[#3]

Thank you so much for the help!

I do have one issue that occurs when doing what you have listed above.

It appears as though I am still retrieving the other Quote #'s that I do not want included in my data set.  What your listed change has done when I add it in my script is simply take the [Quote #] that is the most recently created based on [Date Quote Created], and append the [Dollar Amount] for that quote to every single quote linking to that [Sales Deal #] field.

So, in effect, if a single [Sales Deal #] has 4 quotes with different dates and dollar amounts, that change above is just pulling and changing the data of the other quotes that I don't want pulled to match the quote I want to pull.  Instead, I want to remove all of the other [Quote #] and pull in only the single, most updated Quote #.

Help is very appreciated!!

[#4]

Hi,

Try this

Load

Lastvalue(Quote#),

Sales,

Lastvalue([Date Quote Created]),

Lastvalue([Dollar Amount])

Resident [Table 2 Sales Quotes]

Group by [Sales#] Order by [Date Quote Created]);

Regards,

Xue Bin