0 Replies Latest reply: Mar 28, 2012 8:14 PM by davenguyen RSS

    Proper Way to add User Table

      I'm new to QlikView and my knowledge in database isn't too strong. What is the proper way to handle this situation: I'm downloading data from Salesforce.com. I have an Account, Opportunity, Activity and User data tables as so:

       

      Account

      AccountIdAccountName
      OwnerId
      AAccountA1
      BAccountB2
      CAccountC3

       

      Opportunity

      OpportunityIdOpportunity NameAccountIdOwnerId
      DOppNameDA2
      EOppNameEB3

       

      Activity

      ActivityIdAccountIdOwnerId
      FA1
      GC3

       

      User

      OwnerIdName
      1Joe
      2Gary
      3Larry

       

      When I do this, a Syn Table gets created which links AccountId/OwnerId from the Account/Opportunity table to the User table. I create a table in QlikView and filter by {AccountId = A}, I get this:

       

      NameAccount NameAccountIdOpportunityId
      JoeAccountAA-
      Gary-AD

      - indicates null value

       

      This causes a problem for me as if I build another table where I'm filtering by {OpportunityId = AA}, I want the name of the account, not a null value. So then I reloaded my data to have multiple user tables:

       

       

      Account

      AccountIdAccountName
      AccountOwnerId
      AAccountA1
      BAccountB2
      CAccountC3

       

      Opportunity

      OpportunityIdOpportunity NameAccountIdOpportunityOwnerId
      DOppNameDA2
      EOppNameEB3

       

      Activity

      ActivityIdAccountIdActivityOwnerId
      FA1
      GC3
      HC1

       

      AccountOwner

      AccountOwnerIdAccountOwnerName
      1Joe
      2Gary
      3Larry

       

      OpportunityOwner

      OpportunityOwnerIdOpportunityOwnerName
      1Joe
      2Gary
      3Larry

       

      ActivityOwner

      ActivityOwnerIdActivityOwnerName
      1Joe
      2Gary
      3Larry

       

       

      This removed the duplicates as mentioned before, but when I create this straight table, I can't link the activities since ActivityOwnerId is not the same as AccountOwnerId.

       

      This is what I see (because the third task was created by Joe, but to the account that Larry owns):

      AccountOwnerName# of Accounts# of Activities
      Joe11
      Gary10
      Larry12

       

       

      This is what I would like to display:

      AccountOwnerName# of Accounts# of Activities
      Joe12
      Gary10
      Larry11

       

      This is my formula for # of Accounts: Count(Distinct AccountId)

      This is my formula for # of Activities: Count(ActivityId)

       

      Instead, the # of Activities field will not filter by Joe. I was hoping to do a Set Analysis where I can write "ActivityOwnerId = AccountOwnerId", but I'm not sure how to write the formula.

       

      So I was wondering two things:

      1. What's the proper way to link Account/Opportunity/Activity tables to User table?

      2. If I use multiple user tables, how would I link to the activities?

       

      Thanks,

      Dave