Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

holynumbers
New Contributor II

Outer Join on... Possible?

Hi,

I have two tables

Table1 - Customer

CustomerNameCustomerIDPhoneNumberAddressRegisteredDate
John Doe1+465551111Town Nr12016-09-01
Jane Doe2+465552222Town Nr22016-05-31

Table2 - Calls

ContactIDCallDatePhoneNumberCustomerID
12016-05-01+465552222
22016-05-012
32016-06-01+465552222
42016-06-012
52016-06-01+4655522222

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:

CustomerNameNbrOfCalls
John Doe0
Jane Doe3
<null>2

How should I solve it in the Qlik script?

1 Solution

Accepted Solutions
Not applicable

Re: Outer Join on... Possible?

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

;

1 Reply
Not applicable

Re: Outer Join on... Possible?

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

;