I have a fairly complicated script which loads a set of fact tables, then loads multiple copies of a dates table, renaming fields such that the key field in each Dates table maps to a field in the fact tables. E.g., if my fact table has and order date and a ship date, I get two copies of Dates, Order Dates and Ship Dates, and the date key field is renamed to %OrderDateKey and %ShipDateKey, respectively (mad props to Witherspoon for most of the code). This works, however I get every date in each copy of the dates table. I'd like to delete records from each of the Dates tables if there are no matches to the fact table.
If I was simply joining table A to table B, I'd use the INNER keyword, easy as that. Unfortunately this is not an option due to the complexity of the process for loading and relabeling the dates data. Ideally I'd run something like "DELETE FROM [Order date] WHERE %OrderKey IS NULL", however based on what I've seen, this does not seem to be an option. An equivalent action in the GUI would be to select %OrderKey (or some other field which has a value for every record in the fact tables) and then use Reduce Data | Keep Possible Values.
If that's unclear, here's a simplified outline:
Order Dates Table
|%OrderDateKey||Year and quarter|
A List Based on Order Date Looks Like This
|Year and quarter|
|2012.Q1 - a future date; very confusing for order date|
I'd like to discard the last record from the Order Dates table, as there are no orders with an order date in 2012. I don't really have a preference as to whether or not the date 2011/10/01, which also has no orders but is not a future date, stays or goes.
I've read the doc pages on KEEP and EXISTS(), but they seem to be limited to modifying the LOAD command, and do not let you discard data after loading. Am I missing a command which would achive this? Something like "DELETE FROM [Order Dates] WHERE Date > Today()" would work, but only if I could apply it after the load, to specific tables, as some date tables should include future dates (e.g., expiration date).