Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
Ok so I ended up not dropping the customertmp table to see what happens and now I'm seeing something interesting.
When I mouseover the customertmp table in the tableviewer in qlikview, it shows it only has about 4000 rows, but if I export that table with a store command, it has ~40,000 rows
Could this mean that QlikView is not truly processing left keep or the where clause properly on the table, but only in a limited way where it appears so in the table viewer? This for sure cannot be actual QV functionality, there must be something in my script that is messing this up, no?
So I was about to post my whole script here because it had to be something else in my script that was causing the problem.
And then as I read through it to take out any secure info, i found my issue. There was another table for our teams which was pruning down the list of customer_IDs which happened after my script above, so I wasn't pruning my tmp table at the right time. I put it lower in the script and voila it worked.
So let's chalk this up to me being a bit of a dummy. Thanks to everyone for your help!