
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Keep Left and Where Exists not eliminating rows in table ??
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;
- Tags:
- qlikview_scripting
- « Previous Replies
-
- 1
- 2
- Next Replies »
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I would have expected it to work. Would you be able to share a sample to show where it isn't working?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
So for example, if I display field lists for customer_ID (which at the end of the sample script exists only in the table combined) and customer_ID_CEP (which is supposed to be the pruned down version of the larger customer lilst based on what only already exists in the combined table), I get customer_ID_CEPs which don't exist as customer_ID's and these should have been eliminated...
The following fields are sorted:
So there should be no 1008013250 on the right, because it doesn't exist on the left, and the left keep should have eliminated it.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
May be test load this and try to see if this is working or not:
Combined:
LOAD CUSTOMER_ID;
SELECT
t0.CUSTOMER_ID,
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";
// ********** CUSTOMER TABLE ********** //
Left Keep(Combined)
Customertmp:
LOAD "CUSTOMER_ID" as CUSTOMER_ID_CEP
where exists (CUSTOMER_ID);
SELECT
"CUSTOMER_ID"
From
WFPROD.CUSTOMER;

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Richard,
After your sentence
SELECT
"CUSTOMER_ID",
"CUSTOMER_SUB_TYPE",
"CUSTOMER_TYPE",
"FIRST_NAME",
NAME,
"ONEKEY_ID"
From
WFPROD.CUSTOMER;
Write this:
INNER JOIN (Customertmp)
LOAD Distinct
CUSTOMER_ID,
'Yes' as EXISTS_CUST
RESIDENT Combined;
NoConcatenate
Customer:
Load CUSTOMER_ID_CEP,
"CUSTOMER_SUB_TYPE",
"CUSTOMER_TYPE",
"ONEKEY_ID",
[Full Name]
Resident Customertmp
WHERE EXISTS_CUST = 'Yes';
Drop table Customertmp;
Regards!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
could you explain why this would work but not the original script or the keep left?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Unfortunately I can't test this in this way, because the other fields from the SQL pull also inform what customer_id ends up getting pulle (i.e. only ones for my team), so I tried running this script but then the combined table gives me a large unfiltered list of customer_Id's too instead of eliminating ones not within our team.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
With inner join and where clausule you can filter it for ensure that customers exists on Combined table.
Regards!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Do we need 'where exists (CUSTOMER_ID);' with Keep. I think Qlik should automatically match and reduce based on common fields. Inner Join also can work to fetch common Ids but keep should also work here. Can you try without Where clause,not sure though!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Tried this, same result

- « Previous Replies
-
- 1
- 2
- Next Replies »