topic Re: Joining multiple Resident Tables in QlikView

Joining multiple Resident Tables

bmchale1983 — Wed, 25 Nov 2020 16:16:04 GMT

Only been working in Qlikview a couple months and am currently working with my most complex data set yet. I am trying to tie actuals to targets and am down to one last table I believe, but cannot get data to join from separate resident table. See syntax below, any assistance would be appreciated. Thanks in advance.

ActualTargetBridge:
LOAD
DateType as A, CanonicalDate as B
Resident DateBridge;
LOAD
Region&'-'&Product_Category&'-'&Mortgage_Consultant&'-'&Sales_ID&'-'&Employee_ID as AREALOTargetKey, //Links to Actuals
Mortgage_Consultant&'-'&Sales_ID&'-'&Employee_ID as PreLODateTypeTargetKey,
Region&'-'&Product_Category as PreAREADateTypeTargetKey
Resident Actuals;
LOAD
PreLODateTypeTargetKey&'-'&A&'-'&B as LODateTypeTargetKey, //Links to Employee Target
PreAREADateTypeTargetKey&'-'&A&'-'&B as AREADateTypeTargetKey //Links to Area Target
Resident ActualTargetBridge;

Re: Joining multiple Resident Tables

vishsaggi — Fri, 09 Jun 2017 16:38:27 GMT

Are you getting field not found error?

You are pulling your field in your third Load statement from ActualTargetBridge which does not have any fields with that name?

What is your full Script lines can you paste here?

Re: Joining multiple Resident Tables

bmchale1983 — Fri, 09 Jun 2017 16:46:24 GMT

I really am trying to combine the canonical date and date type(A & B) from date bridge to Targets Keys to connect to target tables.

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;

Re: Joining multiple Resident Tables

bmchale1983 — Fri, 09 Jun 2017 17:22:08 GMT

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');

Re: Joining multiple Resident Tables

bmchale1983 — Fri, 09 Jun 2017 17:57:31 GMT

Now I tried this, but I get error once I get to ActualTargtBridge Load in which fields aren'e found

Temp1:
LOAD
DateType, CanonicalDate
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;

ActualTargetBridge:
LOAD
PreAREADateTypeTargetKey&'-'&DateType&'-'&CanonicalDate as AREADateTypeTargetKey,
PreLODateTypeTargetKey&'-'&DateType&'-'&CanonicalDate as LODateTypeTargetKey,
AREALOTargetKey
Resident Temp1;

DROP Table Temp1;

Re: Joining multiple Resident Tables

vishsaggi — Fri, 09 Jun 2017 19:35:02 GMT

Looks big. Will look into it later. Sorry.

Re: Joining multiple Resident Tables

bmchale1983 — Fri, 09 Jun 2017 20:25:19 GMT

I appreciate that, I was thinking concatenating the actual table and my canonical calendar(date bridge table) might do trick and then linking the targets directly to the concatenated table.