Outer Join on... Possible?

[holynumbers]

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?

[#2 · Accepted solution]

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

;