There are a number of ways to filter data while loading. What I need to do is discard data that has already been loaded, because the filter will not apply until I've renamed fields, which I am doing after the table has been loaded.
I think Flipside's suggestion is close. I believe you need the RESIDENT keyword, in this situation, since the goal is to clone a table in memory rather than load one from a file. What I haven't worked out is a WHERE clause that specifies "the value in field X must appear in field Y in table Z." This is what I have so far:
// Load the fact table, including the fields %OrderDateKey and %ShipDateKey
// Load the dates table as [Order dates], renaming the PK field to %OrderDateKey
// Create a new list of order dates which only incldues those that actually have orders:
[Order dates (trimmed)]:
NOCONCATENATE LOAD * RESIDENT [Order dates] WHERE EXISTS (%OrderDateKey);
DROP TABLE [Order dates]; // Discard the old table
// Load the dates table again, this time as [Ship dates], renaming the PK field to %ShipDateKey, and trim it as done with [Order dates]
However, the EXISTS does not do what I need for this problem, as all date key values do exist, in [Order dates]. So what I'd like to do is something like this:
NOCONCATENATE LOAD * RESIDENT [Order dates] WHERE EXISTS (%OrderDateKey IN Orders);
As a compromise, I could use "WHERE Date(SQLDate) <= Now()". This would limit the dates table to past dates, but it would leave in any dates upon which no orders were placed. This could be good or bad, but at least it's simple, easily explained to the consumers. Using this workaround, I could more simply include the filter when loading the dates table each time, and would not need to LOAD RESIDENT.
.png "Luminary Alumni"){kind=icon}