Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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?
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
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.