Discussion board where members can learn more about Qlik Sense App Development and Usage.
I have two tables
Table1 - Customer
Table2 - Calls
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:
,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:
How should I solve it in the Qlik script?
Go to Solution.
Could this be a solution:
(ooxml, embedded labels, table is Customers);
//Two temp loads to get the right customer info where it is missing
//Load calls together with Customers and with extra info
Outer join (Customers)
if(isnull(PhoneNumber),ApplyMap('Phone',CustomerID),PhoneNumber) as PhoneNumber,
if(isnull(CustomerID),ApplyMap('CustomerID',PhoneNumber),CustomerID) as CustomerID
(ooxml, embedded labels, table is Calls);
//Calculation of calls
count(ContactID) as NbrOfCalls
group by CustomerName