Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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);
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
if i right join i get all my dates. But then i get wrong values otherplaces...
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.
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.
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
ill post my data (the comma is because i just pasted some of the code)
Ill post the whole document
Qlikview document with data in attached
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
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