Proper Way to add User Table

Wed, 28 Mar 2012 23:58:53 GMT

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

topic Proper Way to add User Table in QlikView

Proper Way to add User Table