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
AccountId
AccountName
OwnerId
A
AccountA
1
B
AccountB
2
C
AccountC
3
Opportunity
OpportunityId
Opportunity Name
AccountId
OwnerId
D
OppNameD
A
2
E
OppNameE
B
3
Activity
ActivityId
AccountId
OwnerId
F
A
1
G
C
3
User
OwnerId
Name
1
Joe
2
Gary
3
Larry
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:
Name
Account Name
AccountId
OpportunityId
Joe
AccountA
A
-
Gary
-
A
D
- 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
AccountId
AccountName
AccountOwnerId
A
AccountA
1
B
AccountB
2
C
AccountC
3
Opportunity
OpportunityId
Opportunity Name
AccountId
OpportunityOwnerId
D
OppNameD
A
2
E
OppNameE
B
3
Activity
ActivityId
AccountId
ActivityOwnerId
F
A
1
G
C
3
H
C
1
AccountOwner
AccountOwnerId
AccountOwnerName
1
Joe
2
Gary
3
Larry
OpportunityOwner
OpportunityOwnerId
OpportunityOwnerName
1
Joe
2
Gary
3
Larry
ActivityOwner
ActivityOwnerId
ActivityOwnerName
1
Joe
2
Gary
3
Larry
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
Joe
1
1
Gary
1
0
Larry
1
2
This is what I would like to display:
AccountOwnerName
# of Accounts
# of Activities
Joe
1
2
Gary
1
0
Larry
1
1
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?
