Missing dates in MasterCalendar due to left join

[#1]

Hello,

I have made a left join and hereby i have noticed that i am missing the last dates i have (so my month stops too early)

My Date_Clean has all the dates but my month is missing on the lates dates, so in this example i wont get Month on May (And i have Date_Clean fields from May)

How come?

Sales:

Load

EntryID,

    Upper(CustomerID) as CustomerID,

    DATE#(DATE(CreateDate,'YYYY-MM-DD')) as Date_Clean, //Create DateKey to TimeDimension

    num(DATE#(DATE(CreateDate,'YYYY-MM-DD'))) as Date_CleanNumeric,

    Time(Floor(Frac(CreateDate),1/24/60),'hh:mm') as Time,

    Time(Floor(Frac(CreateDate),1/24/60),'hh:mm') as Time2, //Create HourKey To TimeDimension

    CreateDate,

    SessionIDINT,

    CookieIDINT,CookieIDINT as TempActiveUsersCookies,

    ItemName,

    Price,ItemID,CustomerGUID,ApiMethod,

    SessionUserFeatureINT,

    Capitalize(EventType) as EventType, //Make all events start with a BIG letter first

    Resident Hej

    Order by CreateDate;

  Left join

  TempActiveCookiesUsers:

  LOAD TempActiveUsersCookies,EventType, NumberOfBuys,GroupingBuyers

from D:\QlikviewLoad\QVD\TempActiveUsersCookies.qvd

(qvd);

[#2]

if i right join i get all my dates. But then i get wrong values otherplaces...

[Anonymous]

I don't see a reason based on the script you show.  Maybe it is somewhere else.

BTW, did you try "outer join"?  It should not eliminate anything.

[#4]

Hi,

When outer joining or left joining i only get 175 values in my mastercalendar but i need 189. I get 189 with a right join, but then the values are wrong on EventType, cause it overwrites all the original values.

[maxgro]

please post your data, it seems interesting

but here remove the comma

  Capitalize(EventType) as EventType, //Make all events start with a BIG letter first

    Resident Hej

[#6]

ill post my data (the comma is because i just pasted some of the code)

Ill post the whole document

[#7]

Qlikview document with data in attached

[#8]

update:

If i make a tablebox with my date_clean and sort it by load order i can see that there is one date at the end which is not consistent with the sort order - The latest dates is 2014-04-03 but it should be 2014-05-08 which is the second latest date- else every other date is sorted correctly

[#9 · Accepted solution]

Hi Thomas, I took a look at your script, I would try to change a bit the master calendar section ;  for what I can see the missing date are caused by the wrong value of vMaxDate...

here my suggestion:

Temp:

Load

               min(Date_Clean) as minDate,

               max(Date_Clean) as maxDate

///******Create min/max variables*********              

Resident Sales ;

Let vMinDate = Num(Peek('minDate', 0, 'Temp'));

Let vMaxDate = Num(Peek('maxDate', 0, 'Temp'));

Let vToday = num(Today());

//Let vToday = vMaxDate;

DROP Table Temp;

////*******Temp Calendar******

DateField:

let me know if this help to solve your issue