3 Replies Latest reply on Sep 6, 2013 12:20 AM by anwickes

    Using a sparse lookup with a date range?


      Hey all,


      I have created a table (Benchmark_Lookup) that contains the following columns:

      start_date, end_date, section, benchmark


      I then have numerous logical tables (Eg Business Growth) that will all have a new logical column performing a sparse lookup on the above table to retrieve it's benchmark value.


      In the lookup table, I have multiple rows for the same section with different benchmarks differentiated by the date range that they were in effect.


      I have worked out how to perform this lookup using the following:


      lookup(SPARSE "bla".Benchmark_lookup"."Benchmark" ,0, "bla"."Business_Growth"."section")


      Unfortunately, the above is now bringing back duplicates as it's not working out what date range to select from.

      I have joined the lookup table (using a physical join) to my Time dimension by saying "business_date >= start_date and business_date <= end_Date" but that doesn't seem to work.


      Any ideas?