My Script is as follow
Cisco:
LOAD [End Customer Global Ultimate Name],
Country,
[End Customer HQ Top],
[Sales Level 1],
[Sales Level 2],
[Sales Level 3],
[Sales Level 4],
[Sales Level 5],
[Partner Name],
[Fiscal Period ID],
Year(date#([Fiscal Period ID],'YYYYMM')) as FiscalYear,
//num(month(date#([Fiscal Period ID],'YYYYMM'))) as FiscalMonthNum,
month(date#([Fiscal Period ID],'YYYYMM')) as FiscalMonth,
Date(date#([Fiscal Period ID],'YYYYMM'),'DD-MM-YYYY') as FisDate,
//floor(Date(date#([Fiscal Period ID],'YYYYMM'),'DD-MM-YYYY')) as DateNum,
Week(Date(date#([Fiscal Period ID],'YYYYMM'),'DD-MM-YYYY')) as Week,
FMV,
[Net Bookings],
Group
FROM
[\\arlmssan02\cm$\tp_managers\DBMIGrp\9. Team Members Folders\Karim\Assign_Project\Nikhil.G\CISCO\Copy of June raw.xlsx]
(ooxml, embedded labels, table is Sheet1);
//Left Join
//LOAD * Inline [
//FiscalMonthNum,CalenderMonth, CaldenderMonthNum,FiscalQuarter
//1,Aug,8,Q1
//2,Sep,9,Q1
//3,Oct,10,Q1
//4,Nov,11,Q2
//5,Dec,12,Q2
//6,Jan,1,Q2
//7,Feb,2,Q3
//8,Mar,3,Q3
//9,Apr,4,Q3
//10,May,5,Q4
//11,Jun,6,Q4
//12,Jul,7,Q4 ];
//
////Final:
////LOAD *,
////monthname(Date#(CalenderYear&CalenderMonth,'YYYYMMM')) as CalenderMonthYear,
////date(Date#(CalenderYear&CalenderMonth,'YYYYMMM'),'DD-MM-YYYY') as CalenderDate;
////
////LOAD
////FiscalYear,
////FiscalMonthNum,
////CalenderMonth,
////[Fiscal Period ID],
////if(CaldenderMonthNum>=8,FiscalYear-1,FiscalYear) as CalenderYear
////Resident Cisco;
////
////////
////DROP Table Final;
//
//QuartersMap:
//MAPPING LOAD
//rowno() as Month,
//'Q' & Ceil (rowno()/3) as Quarter
//AUTOGENERATE (12);
Temp:
Load
min(FisDate) as minDate,
max(FisDate) as maxDate
Resident Cisco;
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;
TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
MasterCalendar:
Load
TempDate AS Date,
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
YeartoDate(TempDate)*-1 as CYTD,
YeartoDate(TempDate,-1)*-1 as LYTD,
inyear(TempDate, Monthstart($(varMaxDate)),-1) as Rolling12,
date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,
ApplyMap('QuartersMap', month(TempDate), Null()) as FiscalQuarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;
KK
