4 Replies Latest reply: Nov 27, 2015 3:49 AM by Riley MacDonald RSS

    Looking up latest valid value using ApplyMap

    Riley MacDonald

      Hello,

       

      I have an Excel pricing file which looks like this:

      PriceTable:

      IDDatePrice
      10000347401/10/2015300
      10000347501/09/2015200

       

      And then I have some data in a table which looks like this

       

      QtyTable:

      IDDateQty
      10000347401/10/201510
      10000347501/10/201510

      What I would like to do is make a 4th column in my QtyTable which would calculate the correct value of my quantity. Ideally if there is a match between the ID&Date fields then use that price however if this lookup fails then I would like to take the latest available date for the ID.

       

      Example Row 1) When looking at Row 1 of my QtyTable I would expect to return the price 300 and therefore the 4th column should be 10*300 = 3000 since the ID&Date combination can be matched exactly across both the PriceTable and QtyTable

       

      Example Row 2) When looking at Row 2 of my QtyTable I would expect for this first lookup to fail as the ID&Date combination does not exist in the PriceTable but it should then revert to assigning the latest available price which is 200 in this case so the 4th column should be 10*200 = 2000

       

      Thanks,

      R