Load Distinct keeping duplicate rows

[Jagsfan82]

I was writing script to build a few calendar tables that are linked to a few month fields instead of linked fields.  I would create the initial table based on a date (like a normal master calendar), but also calculate the corresponding month fields to go along with those.  

My plan was then to load the distinct months from the temporary table to build out my other calendars, but instead of having one row per month it has one row per day, which means there are tons of duplicates.

This makes sense from the perspective that there are no distinct loads in my temporary table, but that's not how I thought Load Distinct worked.  I created a workaround trimming the month fields and and starting the calendar from those fields instead, but thought this was pretty weird.  It would not work without the trim.  

Basically my question is why my "AcctgCal" table has duplicate rows and how I could make it not have duplicate rows without that weird intermediate trim table.

[hallquist_nate · Accepted solution]

I think the issue is that your Temp Calendar is built on the date, and formatted to conform to 'YYYYMM'.  Give the code below a try for your TempCalendar table.  I'm sure it could be optimized, but it appears to create what you are looking for in the TempCalendar.  Since the TempCalendar is now at the correct level, the rest of your calendar should work as you are anticipating.

Let vMinDate = Num(AddYears(today(), -4));
Let vMaxDate = Num(Today());

TempCalendar:
Load Distinct
Date(MonthStart(Date($(vMinDate) + IterNo() - 1, 'YYYYMM')), 'YYYYMM') as [Account YearMonth]
AutoGenerate 1 while $(vMinDate) + IterNo() - 1 <= $(vMaxDate);

Good Luck,

Nate

[Brett_Bleess]

Nicholas, did Nate's post do the trick for you?  If so, consider giving him credit by using the Accept as Solution button on that post, which will also let others know that worked.

The only thing I have for you are some Design Blog posts where you might find something to help explain as well:

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Search on the word calendar or date etc., and check out those hits, something there may have some further explanation or give you some other ideas as well.

Regards,
Brett

[Jagsfan82]

Not sure why i didn't apply this to my code, but I did run a quick test and it did work, even when you change the temp table to

Load Distinct
Date($(vMinDate) + IterNo() - 1) as TempDate,
Date(MonthStart(Date($(vMinDate) + IterNo() - 1, 'YYYYMM')), 'YYYYMM') as [Account YearMonth]
AutoGenerate 1 while $(vMinDate) + IterNo() - 1 <= $(vMaxDate);

My accounting month calendar has distinct rows and I still have my temporary date field to link to another master calendar.  I'm assuming maybe what was happenign is Qlik was interpreting the below code as a dual, so the underlying numeric value was "distinct" even though the formatted values were duplicated?  I suppose that makes sense.  The below code also worked, with or without Distinct (Which makes sense)

Load Distinct
Date($(vMinDate) + IterNo() - 1) as TempDate,
Text(Date($(vMinDate) + IterNo() - 1,'YYYYMM')) as [Account YearMonth]
AutoGenerate 1 while $(vMinDate) + IterNo() - 1 <= $(vMaxDate);