It seems that when LOAD DISTINCT is used in a preceding load statement, the subsequent load (above) sees all records, not just the distinct result set. Do I misunderstand or am I missing something?
For example, consider a very simple Caledar table like the following:
LET vMinDate = Num(MakeDate(2012,01,01)); LET vMaxDate = Num(MakeDate(2012,01,31));
Calendar: LOAD DISTINCT Year(Temp_Date) As Year, Month(Temp_Date) As Month, Year(Temp_Date) * 100 + Month(Temp_Date) As [Period] ; LOAD DISTINCT MonthStart($(vMinDate) + IterNo() - 1) As Temp_Date AUTOGENERATE (1) WHILE $(vMinDate) + IterNo() - 1 <= $(vMaxDate);
So the LOAD DISTINCT at the bottom indeed results in just one date (Jan 1, 2012) despite that it iterated through 31 days to get that.
But if I do not also include the DISTINCT predicate in the load on the top (highligted), I'll get 31 records in the calendar.
Is this just something obvious that I've missed? Seems once I LOAD DISTINCT, that resultset in the preceding load should be just the distinct records. After all, if I were to create the first load, then load RESIDENT from that, I would not need to use DISTINCT for the second load.
Thanks so much swuehl and Juan. Yes, I recognized that it required as much, but it just didn't seem right to me, so I wanted to be sure I wasn't missing something. Seems a DISTINCT load, even if a preceding load, should be ... well ... distinct.