topic Re: LOAD DISTINCT and Preceding Loads in QlikView

LOAD DISTINCT and Preceding Loads

Fri, 01 Feb 2013 04:02:46 GMT

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?

Re: LOAD DISTINCT and Preceding Loads

Fri, 01 Feb 2013 09:04:04 GMT

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);

Re: LOAD DISTINCT and Preceding Loads

swuehl — Fri, 01 Feb 2013 10:39:54 GMT

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

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

Re: LOAD DISTINCT and Preceding Loads

Fri, 01 Feb 2013 13:05:54 GMT

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.