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.
Hate to bump but getting close to deadline and I still have no idea how to do this one.
Basically all i want to do is use a value in all of my dimensions that comes from a reference table that has a date range.
I want to use the SPARSE lookup method but it doesn't seem to have any way of selecting from a specific date range.
Neither of these examples help that much.
I understand how the lookup function works but what happens if the lookup table has the following two rows:
EFFECTIVE_FROM EFFECTIVE_TO SECTION SCORE
01/Jan/1900 30/Jun/2013 test 1
01/Jul/2013 06/Jun/2079 test 2
I need to use the following lookup rules:
Business_Date >= Effective_From
Business_Date <= Effective_To
Section = 'Test'
The lookup should therefore only ever return one row as the date is used to find the appropriate range.