I have an Excel pricing file which looks like this:
And then I have some data in a table which looks like this
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 for your help. I ended up fixing this by creating a lookup key of ID&Price and looking this up and then if it failed I would apply another ApplyMap against a sorted list which always shows the latest dates and prices so the latest price is always applied.