Keep Left and Where Exists not eliminating rows in table ??

[richard_cioci]

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; 

[sunny_talwar]

I would have expected it to work. Would you be able to share a sample to show where it isn't working?

[richard_cioci]

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.

[sunny_talwar]

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;

[Anonymous]

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!

[richard_cioci]

could you explain why this would work but not the original script or the keep left?

[richard_cioci]

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.

[Anonymous]

With inner join and where clausule you can filter it for ensure that customers exists on Combined table.

Regards!

[Digvijay_Singh]

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!

[richard_cioci]

Tried this, same result