Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

LOAD DISTINCT and Preceding Loads

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?

1 Solution

Accepted Solutions
Not applicable

Re: LOAD DISTINCT and Preceding Loads

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

3 Replies
Not applicable

Re: LOAD DISTINCT and Preceding Loads

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

MVP
MVP

Re: LOAD DISTINCT and Preceding Loads

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

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

Not applicable

Re: LOAD DISTINCT and Preceding Loads

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

Community Browser