If you are unable to create a new account, please email support@bspsoftware.com

 

How to calculate the number of elements in table A that are not in table B

Started by elgringo, 25 Jul 2023 07:17:02 AM

Previous topic - Next topic

elgringo

Hello Everyone,
Here is the context:
I'm building a Dashboard using a datapack containing several tables (CRM data).
I have a table containing customers (let's call it CUSTOMERS) and a table containing interactions with those customers (let's call it INTERACTIONS).
Of course the same customer can have several interactions available in the INTERACTIONS table and this is easy to identify. But my goal is to identify the customers in the first table that don't have any interactions available or that don't have any interactions in the last 12 months. The end result, I want to provide as a KPI (just a number of customers not contacted ever or not contacted in the last XX months).
I have been unable so far to do that and would be very greatful if any of you could help me or point me towards a ressource on that matter.
Thank you in advance for your help.

cognostechie

You can create a query for customers and another for Interactions. The Interactions query will also have Customer_ID/Customer_Key. Go to query explorer and you will find icons for joins, unions, except etc. Create a third query using except. This is the same as writing a SQL like:

Select
  Cust_ID,
  Cust_Name
from D_Customer C
where not exists (Select Cust_ID from Interactions where Cust_ID eq C.Cust_ID and Date (use symbols for greater than equals) #prompt('pDate','date')#).

This site does not accept symbols and shows an error when I type those in