Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

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