Outer Join on... Possible?

holynumbers — Tue, 07 Mar 2017 16:19:00 GMT

Hi,

I have two tables

Table1 - Customer

CustomerName	CustomerID	PhoneNumber	Address	RegisteredDate
John Doe	1	+465551111	Town Nr1	2016-09-01
Jane Doe	2	+465552222	Town Nr2	2016-05-31

Table2 - Calls

ContactID	CallDate	PhoneNumber	CustomerID
1	2016-05-01	+465552222
2	2016-05-01		2
3	2016-06-01	+465552222
4	2016-06-01		2
5	2016-06-01	+465552222	2

I want to be able to distinct count(ContactID) of the calls that where made after the RegisteredDate.

The problem is that sometimes I have the correct CustomerID, somtimes the correct PhoneNumber and sometimes I have both correct.

If this was a SQL question I would do like this:

SELECT

a.CustomerName

,count(b.ContactID) as NbrOfCalls

FROM Customer as a

outer join Calls as b

on (a.CustomerID = b.CustomerID or a.PhoneNumber = b.PhoneNumber)

and a.RegisteredDate <= b.CallDate

GROUP BY CustomerName

The result in this case should be:

CustomerName	NbrOfCalls
John Doe	0
Jane Doe	3
<null>	2

How should I solve it in the Qlik script?

Re: Outer Join on... Possible?

Thu, 09 Mar 2017 08:55:21 GMT

Could this be a solution:

//load Customers

Customers:

LOAD CustomerName,

CustomerID,

PhoneNumber,

Address,

RegisteredDate

FROM

CustomersCalls.xlsx

(ooxml, embedded labels, table is Customers);

//Two temp loads to get the right customer info where it is missing

Phone:

mapping load

CustomerID,

PhoneNumber

Resident Customers;

CustomerID:

mapping load

PhoneNumber,

CustomerID

Resident Customers;

//Load calls together with Customers and with extra info

Calls:

Outer join (Customers)

LOAD ContactID,

CallDate,

if(isnull(PhoneNumber),ApplyMap('Phone',CustomerID),PhoneNumber) as PhoneNumber,

if(isnull(CustomerID),ApplyMap('CustomerID',PhoneNumber),CustomerID) as CustomerID

FROM

CustomersCalls.xlsx

(ooxml, embedded labels, table is Calls);

//Calculation of calls

Calc:

load

CustomerName,

count(ContactID) as NbrOfCalls

resident Customers

where RegisteredDate<=CallDate

group by CustomerName

;

topic Re: Outer Join on... Possible? in App Development

Outer Join on... Possible?

Re: Outer Join on... Possible?