3 Replies Latest reply: Aug 6, 2012 10:03 PM by Bin Xue RSS

    Load only the most updated record among multiple listed

      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 CreatedDollar Amount
      11111111231/4/2012456.00
      11111121235/3/20123324.00
      11111132343/5/20124534.00
      111111423412/4/20114443.00
      11111152347/5/20128988.00
      11111163455/4/20127788.00
      11111173455/7/20127650.00

       

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

       

      Help is appreciated!

        • Re: Load only the most updated record among multiple listed
          Nagaian Krishnamoorthy

          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#];

            • Re: Load only the most updated record among multiple listed

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