1 Reply Latest reply: Mar 9, 2017 3:55 AM by Eva Reinmuller RSS

    Outer Join on... Possible?

    John Mårtensson

      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?

        • Re: Outer Join on... Possible?
          Eva Reinmuller

          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

          ;