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.