Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
;
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
;