Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

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

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.

1 Solution

Accepted Solutions
jason_michaelid
Not applicable

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

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

   A,

   B,

   C

From...;

Tab2:

NoConcatenate

Load

   A,

   B,

   C

From...;

Hope this helps,

Jason

3 Replies
Not applicable

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

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?

jason_michaelid
Not applicable

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

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

   A,

   B,

   C

From...;

Tab2:

NoConcatenate

Load

   A,

   B,

   C

From...;

Hope this helps,

Jason

Not applicable

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

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.