LOAD DISTINCT and Preceding Loads

[#1]

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.

Thoughts?

[#2 · Accepted solution]

It seems like the DISTINCT is done on the last LOAD, so my recomendation is you remove the inner DISTINCT, and you will get what you want:

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

   MonthStart($(vMinDate) + IterNo() - 1) As Temp_Date

AUTOGENERATE (1)

WHILE $(vMinDate) + IterNo() - 1 <= $(vMaxDate);

JG

[swuehl]

Yes, I think that's what Henric also mentioned here:

http://community.qlik.com/message/288799#288799

[#4]

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.