topic Re: Missing dates in MasterCalendar due to left join in QlikView

Missing dates in MasterCalendar due to left join

Tue, 13 May 2014 17:18:43 GMT

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

Re: Missing dates in MasterCalendar due to left join

Tue, 13 May 2014 18:40:37 GMT

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

Re: Missing dates in MasterCalendar due to left join

Anonymous — Tue, 13 May 2014 18:44:56 GMT

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.

Re: Missing dates in MasterCalendar due to left join

Tue, 13 May 2014 18:58:00 GMT

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.

Re: Missing dates in MasterCalendar due to left join

maxgro — Tue, 13 May 2014 18:59:12 GMT

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

Re: Missing dates in MasterCalendar due to left join

Tue, 13 May 2014 19:01:11 GMT

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

Ill post the whole document

Re: Missing dates in MasterCalendar due to left join

Tue, 13 May 2014 19:19:48 GMT

Qlikview document with data in attached

Re: Missing dates in MasterCalendar due to left join

Tue, 13 May 2014 20:03:47 GMT

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

Re: Missing dates in MasterCalendar due to left join

Tue, 13 May 2014 21:32:21 GMT

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