1 Reply Latest reply on Jun 17, 2013 6:41 AM by Brad Llewellyn

    Return attributes from max value of a joined table (data blending)

    Martin Hoffmann

      Hello,

       

      I'm blending a list of selected company in excel to a huge temporal database extracted in a TBE.

       

      The selected list in excel look like this:

      Usage_Namecompany_idStudy area
      Company A22554646654West coast
      Company B54656464646West coast
      Company C48494949499East coast
      Company D49646464646East coast

       

      The database look like (stored in tbe):

      company_idOfficial_NamedateInteresting_attributeMany other attributes...
      22554646654Company A2013

      Good

      ...
      22554646654Company A

      2012

      Good...
      22554646654Company A2011Bad...
      54656464646Company B2006Good...
      48494949499Company C2007Pretty bad...
      48494949499Company C2008Bad
      48494949499Company C2010Bad

       

      I need to make 'selected list' the primary table because some company might not be listed in the database and this is also a valuable information. Also it contain really fewer row.


      By using data blending I managed to get a view like this except for the last column:

      selected.Usage_Nameselected.company_iddatabase.datedatabase.max(date)Interesting_attribute
      for database.max(date)
      Company A22554646654*2013Good

      Company B

      5465646464620062006Good
      Company C48494949499*2010Bad
      Company D49646464646NULLNULLNULL

      What I would like to see in this last column is the last known value for 'interesting attribute' in the database which must fit the max(date) value in the database. Obviously this should also provide access to 'Many other attributes...' of the database following the same process.

       

      It tried something like: date =windows_max(date) but the data-blending context don't seem to allow this...

       

      Thank for answering and sorry for my English, I'm french.

       

      PS: Also because of privacy and data copyright I'm not allowed to upload the (heavy) twbx, but i can provide as much additional information as required.