Here is entire script............
SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
Actuals:
LOAD Lead_Number,
Lead_Create_Date,
Application_Date,
Loan_Amount,
Registration_Date,
Closing_Date,
Cancellation_Date,
Denial_Date,
Disbursement_Occurred_Date,
Property_City,
Property_State,
Property_Zip_Code,
Loan_Officer_Name,
PHH_Loan_Officer_ID,
Employee_Active_Indicator,
Loan_Officer_ID,
Sales_Manager,
Referring_Employee,
Referring_Employee_ID,
Branch_Number,
Branch_Name,
Region,
Purchase_Or_Refi,
Loan_Purpose_Code,
Loan_Status,
UW_Status,
First_Approval_Date,
Approved_Date,
PHH_STAT_CDE,
Underwriting_Submission_Date,
Discount_Program,
Program_Number,
Program_Description,
Lock_Expiration_Date,
Processor_Name,
Assistance_Provider_1,
Assistance_Amount_1,
Assistance_Provider_2,
Assistance_Amount_2,
Appraisal_Received_Date,
Appraisal_2_Recieved_Date,
Scheduled_Closing_Date,
Closing_Package_Received_Date,
Note_Recieved_Date,
Appraisal_Inspection_Date,
Appraisal_Confirmed_Date,
Credit_Contingency_Date,
Closed_Ind,
Registration_Ind,
Cancelled_Ind,
Referral_Ind,
Lock_Expiration_Status,
Product_Category,
Product_Segmentation,
Private_Banking_Ind,
Day_Count_Reg_Closing,
Day_Count_Lead_Reg,
Lead_Count,
Loan_Count,
Extract_Date,
Num_Days_Lock_Expiration,
Loan_Program_Code,
Acct_Sold_Status,
Foreign_National_Indicator,
Self_Sourced,
Lead_Status_Code,
APPLICATION_STATUS,
Level_Product_Code_2,
Level_Product_Code_3,
Program_Type,
LOCK_DT_TM,
Loan_ID,
Specialized_Sales_Manager,
Sales_Area,
Mortgage_Consultant,
Sales_ID,
Employee_ID,
Lead_Number&'-'&Region&'-'&Product_Category&'-'&Mortgage_Consultant&'-'&Sales_ID&'-'&Employee_ID as AREALOTargetKey
FROM
[\\qlikviewnau.us.hsbc\Content\US RBWM\Sources\AOP Source\AOP_ExtractvQlik.xlsx]
(ooxml, embedded labels, table is Sheet1);
AreaTargets:
LOAD
Area&'-'&Product&'-'&Type&'-'&Temp_DT as AREADateTypeTargetKey,
Type as AREA_Type,
Temp_DT as AREA_Date,
Target_Month_Days as AREA_Month_Days,
Monthly_Target as AREA_Total_Monthly_Target,
Region_Monthly_Targets as AREA_Monthly_Targets,
Region_Daily_Targets as AREA_Daily_Targets
FROM
[\\qlikviewnau.us.hsbc\Content\US RBWM\Sources\AOP Source\Region_Targets_2016.xlsx]
(ooxml, embedded labels, table is Sheet1);
EmployeeTargets:
LOAD
Mortgage_Consultant&'-'&Sales_ID&'-'&Employee_ID&'-'&Type&'-'&Temp_DT as LODateTypeTargetKey,
Type as LO_Type,
Temp_DT as LO_DATE,
Target_Month_Days as LO_Month_Days,
Peer_group as LO_Peer_Group,
LO_Monthly_Targets,
LO_Daily_Targets
FROM
[\\qlikviewnau.us.hsbc\Content\US RBWM\Sources\AOP Source\LO_Targets_2016.xlsx]
(ooxml, embedded labels, table is Sheet1);
MasterCalendarCLOSINGDATE:
Load
TempDate AS Closing_Date,
week(TempDate) As WeekCD,
Year(TempDate) As YearCD,
Month(TempDate) As MonthCD,
Day(TempDate) As DayCD,
'Q' & ceil(month(TempDate) / 3) AS QuarterCD,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYearCD,
WeekDay(TempDate) as WeekDayCD
;
//=== Generate a temp table of dates ===
LOAD
date(mindate + IterNo()) AS TempDate
,maxdate // Used in InYearToDate() above, but not kept
WHILE mindate + IterNo() <= maxdate;
//=== Get min/max dates from Field ===/
LOAD
min(FieldValue('Closing_Date', recno()))-1 as mindate,
max(FieldValue('Closing_Date', recno())) as maxdate
AUTOGENERATE FieldValueCount('Closing_Date');
MasterCalendarREGDATE:
Load
TempDate AS Registration_Date,
week(TempDate) As WeekRD,
Year(TempDate) As YearRD,
Month(TempDate) As MonthRD,
Day(TempDate) As DayRD,
'Q' & ceil(month(TempDate) / 3) AS QuarterRD,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYearRD,
WeekDay(TempDate) as WeekDayRD
;
//=== Generate a temp table of dates ===
LOAD
date(mindate + IterNo()) AS TempDate
,maxdate // Used in InYearToDate() above, but not kept
WHILE mindate + IterNo() <= maxdate;
//=== Get min/max dates from Field ===/
LOAD
min(FieldValue('Registration_Date', recno()))-1 as mindate,
max(FieldValue('Registration_Date', recno())) as maxdate
AUTOGENERATE FieldValueCount('Registration_Date');
MasterCalendarLEADDATE:
Load
TempDate AS Lead_Create_Date,
week(TempDate) As WeekLCD,
Year(TempDate) As YearLCD,
Month(TempDate) As MonthLCD,
Day(TempDate) As DayLCD,
'Q' & ceil(month(TempDate) / 3) AS QuarterLCD,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYearLCD,
WeekDay(TempDate) as WeekDayLCD
;
//=== Generate a temp table of dates ===
LOAD
date(mindate + IterNo()) AS TempDate
,maxdate // Used in InYearToDate() above, but not kept
WHILE mindate + IterNo() <= maxdate;
//=== Get min/max dates from Field ===/
LOAD
min(FieldValue('Lead_Create_Date', recno()))-1 as mindate,
max(FieldValue('Lead_Create_Date', recno())) as maxdate
AUTOGENERATE FieldValueCount('Lead_Create_Date');
DateBridge:
LOAD
Lead_Number,Lead_Create_Date as CanonicalDate, 'Leads' as DateType
Resident Actuals;
LOAD Lead_Number,Registration_Date as CanonicalDate, 'Registrations' as DateType
Resident Actuals;
LOAD Lead_Number,Closing_Date as CanonicalDate, 'Fundings' as DateType
Resident Actuals;
ActualTargetBridge:
LOAD
DateType as A, CanonicalDate as B
Resident DateBridge;
LOAD
Region&'-'&Product_Category&'-'&Mortgage_Consultant&'-'&Sales_ID&'-'&Employee_ID as AREALOTargetKey,
Mortgage_Consultant&'-'&Sales_ID&'-'&Employee_ID as PreLODateTypeTargetKey,
Region&'-'&Product_Category as PreAREADateTypeTargetKey
Resident Actuals;
LOAD
PreLODateTypeTargetKey&'-'&A&'-'&B as LODateTypeTargetKey,
PreAREADateTypeTargetKey&'-'&A&'-'&B as AREADateTypeTargetKey
Resident ActualTargetBridge;
MasterCalendarCANONICAL:
Load
TempDate AS CanonicalDate,
week(TempDate) As WeekCAN,
Year(TempDate) As YearCAN,
Month(TempDate) As MonthCAN,
Day(TempDate) As DayCAN,
'Q' & ceil(month(TempDate) / 3) AS QuarterCAN,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYearCAN,
WeekDay(TempDate) as WeekDayCAN
;
//=== Generate a temp table of dates ===
LOAD
date(mindate + IterNo()) AS TempDate
,maxdate // Used in InYearToDate() above, but not kept
WHILE mindate + IterNo() <= maxdate;
//=== Get min/max dates from Field ===/
LOAD
min(FieldValue('CanonicalDate', recno()))-1 as mindate,
max(FieldValue('CanonicalDate', recno())) as maxdate
AUTOGENERATE FieldValueCount('CanonicalDate');
