Joining SQL table with Master Calendar. Issue with resident load?

Thu, 29 Mar 2012 19:02:07 GMT

Ok I am joining our employee table with our master calendar so that I can do some other links correctly with the date and employee fields. However when I go to create my final table after all the joining it is not staying and the table is gone. Is there something wrong with my script?

Calendar:  
LET vDateMin = Num(MakeDate(2010,1,1));  
LET vDateMax = Floor(YearEnd(Today()));  
LET vDateToday = Num(Today());  
LET PD = Date(makedate(2010,1,08));
TempCalendar:  
LOAD 
$(vDateMin) + RowNo() - 1 AS DateNumber,  
Date($(vDateMin) + RowNo() - 1) AS TempDate  
AUTOGENERATE 1  
WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);  
Calendar:  
LOAD 
Date(TempDate) AS CalendarDate,  
//Date(TempDate) AS  calendarlink,
if(if(mod(floor(TempDate)-'$(PD)',14)=0,TempDate)<=date(Today()+18),if(mod(floor(TempDate)-'$(PD)',14)=0,TempDate) )as _PayDay,
// Standard Date Objects
Day(TempDate) AS CalendarDayOfMonth,  
WeekDay(TempDate) AS CalendarDayName,  
Week(TempDate) AS CalendarWeekOfYear,  
Month(TempDate) AS CalendarMonthName,  
'Q' & Ceil(Month(TempDate)/3) AS CalendarQuarter,  
Year(TempDate) AS CalendarYear,  
// Calendar Date Names  
WeekName(TempDate) as CalendarWeekNumberAndYear,  
MonthName(TempDate) as CalendarMonthAndYear,  
QuarterName(TempDate) as CalendarQuarterMonthsAndYear,  
// Start Dates  
DayStart(TempDate) as CalendarDayStart,  
WeekStart(TempDate) as CalendarWeekStart,  
MonthStart(TempDate) as CalendarMonthStart,  
QuarterStart(TempDate) as CalendarQuarterStart,  
YearStart(TempDate) as CalendarYearStart,  
// End Dates  
DayEnd(TempDate) as CalendarDayEnd,  
WeekEnd(TempDate) as CalendarWeekEnd,  
MonthEnd(TempDate) as CalendarMonthEnd,  
QuarterEnd(TempDate) as CalendarQuarterEnd,  
YearEnd(TempDate) as CalendarYearEnd,  
// Combo Date Examples  
'Q' & Ceil(Month(TempDate)/3) & '/' & Year(TempDate) AS CalendarQuarterAndYear,  
Year(TempDate) & '/' & 'Q' & Ceil(Month(TempDate)/3) AS CalendarYearAndQuarter,  
'Wed ' & DayStart(WeekStart(TempDate) + 3) as CalendarWednesdays  
RESIDENT TempCalendar ORDER BY TempDate ASC;  
DROP TABLE TempCalendar;  
LET vDateMin = Num(MakeDate(2010,1,1));  
LET vDateMax = Floor(YearEnd(AddMonths(Today(), 12)));  
LET vDateToday = Num(Today());  
Calendarnew:  
LOAD 
date( CalendarDate,'WWW') as DayofWeekName,
CalendarDate as 'Calendar Date',
month(CalendarDate) as 'Calendar Month',
year(CalendarDate) as 'Calendar Year',
MonthName(CalendarDate) as 'Calendar Month-Year'
RESIDENT Calendar;
Employee:
join (Calendarnew)
LOAD 
    company & '-' & jcdept as jclink,
    company & '-' & expensecode as expenselink,
    company & '-' & empid as laborheadlink,
    company & '-' & empid as laborheadlink2,    
    company as Company,
    empid as 'Employee ID',
    name as Employee,
    shift as Shift,
    laborrate as 'Labor Rate',
    if(payroll=1,'Employee','Contract') as 'On Payroll',
    if(empstatus='A','Active','Inactive') as Status,
    expensecode as 'Expense Code',
    jcdept as 'JC Dept. Code',
  Plant,
  Department,
   [Plant Short],
     if(productionworker=1,'Shop','Office') as 'Worker';
SQL SELECT company,
    empid,
    name,
    shift,
    laborrate,
    payroll,
    empstatus,
    expensecode,
    jcdept,
    number01,
    number02,
    productionworker
FROM epicor904.dbo.empbasic;
NewEmployeeCal:
Load 
DayofWeekName,
[Calendar Date],
[Calendar Month],
[Calendar Year],
[Calendar Month-Year],
jclink,
expenselink,
laborheadlink & '-' & [Calendar Date] as laborheadlink,
laborheadlink & '-' & [Calendar Date] as laborheadlink2,    
Company,
[Employee ID],
Employee,
Shift,
[Labor Rate],
[On Payroll],
Status,
[Expense Code],
[JC Dept. Code],
Plant,
Department,
[Plant Short],
Worker
RESIDENT Calendarnew;
Drop Table Calendarnew;

Note that I removed some of the calculations for confidentiality sake in employee table and just left the new field name.

I should have a new table labeled NewEmployeeCal but there is none.

Joining SQL table with Master Calendar. Issue with resident load?

Tue, 03 Apr 2012 15:32:36 GMT

Ok I have discovered this only happens when I take all fields from the Calendarnew: load into the final load. I created a field 'A' as new in the Calendarnew: table and did not take it to the final NewEmployeeCal: table and it works fine. Why would this cause this issue?

Joining SQL table with Master Calendar. Issue with resident load?

Jason_Michaelides — Tue, 03 Apr 2012 17:02:15 GMT

Took me ages to figure this one out a couple of years ago - and then I only discovered the reason by accident!!

If you are loading a table with exactly the same structure as a table already resident in memory then QlikView will join them together (but doesnt tell you it has done so!) That's why the addition of your new dummy field prevents this. The proper way to prevent this automatic joining us to preced the second LOAD statement with "NoConcatenate".

So:

Tab1:

Load

From...;

Tab2:

NoConcatenate

Load

From...;

Hope this helps,

Jason

Joining SQL table with Master Calendar. Issue with resident load?

Tue, 03 Apr 2012 17:06:28 GMT

Yeah, after I posted the last reply I thought it might be doing that. Remembered reading that if 2 tables have the exact same fields it would combine them and was guissing that was what happened so when I dropped the original tables it took the new one with it.

topic Joining SQL table with Master Calendar. Issue with resident load? in QlikView

Joining SQL table with Master Calendar. Issue with resident load?

Joining SQL table with Master Calendar. Issue with resident load?

Joining SQL table with Master Calendar. Issue with resident load?

Joining SQL table with Master Calendar. Issue with resident load?