3 Replies Latest reply: Sep 5, 2013 7:20 PM by anwickes RSS

    Using a sparse lookup with a date range?

    anwickes

      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?