Hi Everyone,
I am pulling in some tables via SQL into Qlik from a CRM database. Our subset of customers is much smaller than the entire list of customers available in the CRM, so I have been using the main table of events to eliminate the customers we haven't called on from our customer table.
I had been previously loading the events first and then loading the customer list with a where exists(customer_id) clause which seemed o be working great. In my table viewer I could see that this dropped the customer list from ~40,000 to ~4,000.
Now for a separate reason, I need to separate this table from the main events table but I still need to keep it reduced. I initially just changed the naming of the common field/key between the two tables, but when I did this the number of customers shot back up to 40,000. I even tried leaving in the original common field and using the where clause and then loading another table using the customer table as resident and then dropping the initial customer table, but it doesn't give me a reduced number of customers.
I have also tried left (keep) to the original event table but it is not reducing the number of customer_id's, and I don't see what I'm doing wrong.
I've included part of my code below:
Combined is my master event table which I want to use to eliminate unneeded customer_id's from the customer table:
Combined:
LOAD *,
date([Call Date] , 'DD-MM-YYYY') as [Call Date Short] //abbreviate out the time leaving the date
;
LOAD
AFFILIATION_ID,
ALIGNMENT_ID,
CUSTOMER_ID,
CUSTOMER_ID as customer_rating_id,
EMPLOYEE_ID,
ENTRY_DATE as [Call Entry Date],
EVENT_ID,
EVENT_SUB_TYPE,
EVENT_TYPE,
INTERACTION_CHANNEL,
START_DATE_TIME as [Call Date],
DayStart(START_DATE_TIME) as CalCallDate,
"ADDRESS_ID",
"PRODUCT_ID",
"PRODUCT_ID" as ProdIDDBS,
"REACTION_TYPE",
"SEQUENCE",
CITY as City,
"LINE_1_ADDRESS" as [Street Address],
OFF_SUBDIVISION_COD_3 as Province,
"POSTAL_AREA" as FSA,
"ALIGNMENT_NAME",
"TEAM_ID",
EXTERNAL_ID_1 as AlignID,
TEAMNAME as Team,
STATUS_CHANGE_DATE,
PRODUCT_ID&CUSTOMER_ID&EMPLOYEE_ID as CEP_ID
;
SELECT
t0.AFFILIATION_ID,
t0.ALIGNMENT_ID,
t0.CUSTOMER_ID,
t0.EMPLOYEE_ID,
t0.ENTRY_DATE,
t0.EVENT_ID,
t0.EVENT_SUB_TYPE,
t0.EVENT_TYPE,
t0.INTERACTION_CHANNEL,
t0.START_DATE_TIME,
t1."ADDRESS_ID",
t2."PRODUCT_ID",
t2."REACTION_TYPE",
t2."SEQUENCE",
t4.CITY,
t4."LINE_1_ADDRESS",
t4."POSTAL_AREA",
t4."OFF_SUBDIVISION_COD_3",
t6."ALIGNMENT_NAME",
t6."TEAM_ID",
t6."EXTERNAL_ID_1",
t7.NAME as TEAMNAME,
t0.STATUS_CHANGE_DATE
FROM
WFPROD.EVENT t0,
WFPROD.AFFILIATION t1,
WFPROD."EVENT_DETAIL" t2,
WFPROD.ADDRESS t4,
WFPROD.ALIGNMENT t6,
WFPROD.TEAM t7
where t0.AFFILIATION_ID = t1.AFFILIATION_ID
and t0.EVENT_ID = t2."EVENT_ID"
and t1."ADDRESS_ID" = t4."ADDRESS_ID"
and t0.START_DATE_TIME >= '$(vPULLDATE)' //YYYY-MM-DD
and t0.ALIGNMENT_ID = t6.ALIGNMENT_ID
and t6."TEAM_ID" = t7."TEAM_ID"
;
// ********** RATING TABLE ********** //
Rating:
load
"CLIENT_ALIGNMENT_ID",
"SCRAP",
"PRODID" as ProdID,
"RATING_VALUE";
select A.external_id_1 as client_alignment_id,
C.onekey_id as SCRAP,
PR.external_id_1 as ProdID,
R.value_min as rating_value
//C.customer_id as customer_rating_id
from WFPROD.rating R
join WFPROD.dn_rating_attribute D
on R.dn_rating_attribute_id = d.dn_rating_attribute_id
join WFPROD.customer C
on R.customer_id = C.customer_id
join WFPROD.alignment A
on R.alignment_id = A.alignment_id
left outer join WFPROD.product PR
on R.product_id = PR.product_id
left outer join WFPROD.period P
on R.period_id = P.period_id
where D.external_id_1 = 'ENGAG'
and value_min is not null and onekey_id is not null
;
Left join (Rating)
LOAD "ALIGNMENT_ID" as CLIENT_ALIGNMENT_ID,
"EMPLOYEE_ID" ;
SQL SELECT "ALIGNMENT_ID",
"EMPLOYEE_ID"
FROM WFPROD.ALIGNMENT;
// ********** CUSTOMER TABLE ********** //
Left Keep(Combined)
Customertmp:
LOAD "CUSTOMER_ID" as CUSTOMER_ID_CEP,
"CUSTOMER_ID",
"CUSTOMER_SUB_TYPE",
"CUSTOMER_TYPE",
"FIRST_NAME",
NAME,
"ONEKEY_ID",
FIRST_NAME & ' ' & NAME as [Full Name] //combine names for easier selection
where exists (CUSTOMER_ID);
SELECT
"CUSTOMER_ID",
"CUSTOMER_SUB_TYPE",
"CUSTOMER_TYPE",
"FIRST_NAME",
NAME,
"ONEKEY_ID"
From
WFPROD.CUSTOMER;
NoConcatenate
Customer:
Load CUSTOMER_ID_CEP,
"CUSTOMER_SUB_TYPE",
"CUSTOMER_TYPE",
"ONEKEY_ID",
[Full Name]
Resident Customertmp;
Drop table Customertmp;
