7 Replies Latest reply: Jun 27, 2017 10:32 AM by Weston Kingsley RSS

    Count/Find Unique Sets

    Weston Kingsley

      Hello Qlik Community!

       

      I am looking to retrieve a list of unique People IDs that have been visited by account managers.

       

      I have three tables: Donors, Account Managers, and Contacts. The fields are like:

       

      Donors:

      Donor ID,

      First Name,

      Last Name,

      Assigned Manager

       

      AccountManagers:

      Manager ID,

      Manager First Name,

      Manager Last Name (edited)

       

      Contacts:

      Contact ID,

      Contact Type,

      Contact Date,

      Manager ID,

      Donor ID

       

      I'd like to be able to find out how many Visits (value in Contact Type) were made by an Account Manager to UNIQUE Donors. So if we have managers that are reporting a high quantity of visits, but they're all to the same 3 people, we want to tell them to branch out. I'm guessing this will use some type of Set Analysis or Aggr(), but I'm open really to any solutions.

       

      Thanks for the help!

        • Re: Count/Find Unique Sets
          Sunny Talwar

          May be this

           

          Dimension

          Manager First Name

          Manager Last Night


          Expression

          Count(DISTINCT [Donor ID])

          • Re: Count/Find Unique Sets
            Weston Kingsley

            Here are some examples of the data:

             

            Donors:

            Load * Inline [

            [Donor ID], [First Name], [Last Name], [Manager ID]

            001,John,Smith,101

            002,Jane,Doe,101

            003,Albert,Einstein,202

            004,Bill,Nye,202

            005,Stephen,Hawking,303

            006,Neil,Tyson,404

            007,James,Bond,505];

             

            AcountManager:

            Load * Inline [

            [Manager ID],[Manager First Name],[Manager Last Name]

            101,Michael,Jordan

            202,Lebron,James

            303,Steph,Curry

            404,Kevin,Durant

            505,Larry,Bird

             

            Contacts:

            Load * Inline [

            [Contact ID],[Contact Type],[Manager ID],[Donor ID]

            9001,Visit,101,001

            9002,Visit,101,002

            9003,Phone,101,001

            9004,Email,202,004

            9005,Visit,202,003

            9006,Visit,202,003

            9007,Visit,202,003

            9008,Visit,202,004

            9009,Visit,303,005

            9010,Email,303,005

            9011,Phone,404,006

            9012,Email,404,006

            9013,Visit,505,007

            9014,Phone,505,007

            9015,Email,505,007

             

            So the "Visit" Counts to Unique Donors should be as follows:

             

            Manager IDNumber of Unique Donors VisitedTotal VisitsTotal Contacts
            101223
            202245
            303112
            404002
            505113
            • Re: Count/Find Unique Sets
              Andrew Walker

              Hi Weston,

              I can't improve on Sunny's solution but can I make a suggestion about your data model? There's a synthetic table that I think highlights a little flaw in the logic. If the Manager ID field really belongs in the Donors table then, for the purposes of this exercise, it's not needed in the Contacts table too .

               

              Maybe the Contacts table is the more natural home for Manager ID. If you remove this field from Donors we still get the correct result.

               

              I think the Manager ID field is being overworked. It's not fair to ask it to tell us who the current manager is for a donor and also tell us who made each contact because over time contacts to a donor could be made by different managers. It might be that your database has more than one id field to meet these different requirements.

               

              Good luck

               

              Andrew

                • Re: Count/Find Unique Sets
                  Weston Kingsley

                  Totally agree about the model. I'm open to reworking it, but wasn't sure if there's an obvious way around it; I'm still learning about creating Link/Key tables in order to get everything to fit nicely from time to time. This is a solution I'll be working on though...

                   

                  To your suggestion, the Manager ID belongs in it's own table. In theory, an Account Manager SHOULDN'T contact another Manager's Donors, but it happens from time to time by necessity. Do you have any suggestion on how to create a model where it works to get both assigned donors and contacted donors?

                   

                  As far as the requirements go, it is necessary to be able to both tell who the ASSIGNED Manager is, as well as finding out who made the contact. One of the metrics we're trying to track is, basically, an overall view at ALL Contact Activity AND Contact Activity to only assignments. It's a pretty involved study, one I don't necessarily see all the value in, but it's a requirement nonetheless (I just do what I'm told!).