Iterate through Date Values

[robertbrooks]

I need to determine the difference between Report Date and Begin Date for every possible combination of Report and Begin Dates. Based on the result I will create a dimension field that identifies the difference in each row as either greater than or less than 90.

How do I script this in the load statement to create the Desired Results Table shown below?

[dwforest · Accepted solution]

[TempDates]:

LOAD ReportDate

FROM Table1;

JOIN

LOAD BeginDate

FROM Table2;

Because there are no matching fields to join you will get a cartesean product.

[FinalDates]:

LOAD

ReportDate, BeginDate, ReportDate-BeginDate as BucketDim

RESIDENT [TempDates];

DROP TABLE [TempDates];

[robertbrooks]

Thank you very much! This worked perfectly.