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?
Thanks,
Dave
