I'm looking for help with a solution to joining a data table to another that has time-phased or effective dated information. To explain further, I've attached a .dxp with sample data. In my 'sales information' table I simply have sales data by week-start-date by item. In another, I have item and a product category attribute, but this second table is 'effective dated.' By that I mean that the product attribute changes over time (with start and end dates).
So if I tried to add columns in order to add the product category to the sales data, there are three choices on categories, based on the date. I can't really join on the week start date as well, becuase the date ranges in the attribute table don't exactly match the date in the sales table.
What I want it to do is look at the 'week start date' of the sales record, and find the appropriate item category attribute based on the effective start and end date on the attribute table.
Please look at the sample data. I appreciate any help you can offer on this.