2 Replies Latest reply on Dec 2, 2013 1:39 PM by Mike Penney

    Looking up rates in an external table for use in dated transactions

    Mike Penney


      I have a list of dated transactions showing the transaction date, source name and sum of quantity for that source according to the date. I also have an external table with rates listed for each source and also dated according to rate changes.


      I would like to draw from the external table to insert the appropriate rate for each transaction according to the source and date of the transaction. The transaction data is from an SQL database and the Rate data is on an Excel file (Aggregated). Also note that the Transactions do not occur on the exact dates of the rate changes so I need to draw the correct rate according to the date-range where the transaction occurred. The objective is to multiply the quantity measure by the corresponding rate for each transaction.

       

      This looks like a simple one and I have done more difficult viz's but this one escapes me. I tried to workup a calculated field to filter the rates for use with the Transactions and I left that calculated field in its current (error) state so you could see what I was trying to do. Please see the attached packaged workbook.

       

      Can anyone help with this?