Counting Created and Owned for a Particular User

[Leo]

Hello guys,

I have a table similar to this one for Services Request:

SR#	Creator ID	Owner ID	Date
sr12eb13	1	1	18-Jan
sr12eb14	2	1	18-Jan
sr12eb15	3	1	20-Jan
sr12eb16	4	3	20-Jan

And a second table like this one Team members:

User ID	User Name	Position
1	John	Specialist
2	Paul	Annalist
3	George	Annalist
4	Tommy	Annalist

This is the result I need in a table on chart time:

Filter User Name

John

Date	Created	Owned
18-Jan	1	2
20-Jan	0	1

I have tried Several things and I'm able to get bot number separated. But I cannot wrap my head around get the two of them in the same table.

Additional challenges:

• The user ID field in Teams Contain the information for both Creator ID and Owner ID in the Service Request Table.
• The same user  can create tickets that he may own or that may be owned by different users.
• On the real data set  we have 250K rows for the Service Request Table.
• I'm Working on a server and cannot request any plugin

What do I already have, or tried:

• I have my Master Calendar up and running, and linked with the Service Request Table.
• I have a functional application from where I can get many different Tables and KPI's already.
• I tried loading Teams table twice. Once as Creators, once as Owners. Here I manage to get both values in separated tables.  However I need them in one table.
• I tried creating a flag  set to 1 if Creator ID = Owner ID.
• I tried Set Analysis to include the OwnerIDs that are not counted for a particular Creator ID but only when the Creator ID I selected is in the OwnerIDs Field. I believe this is a god path but I cannot see through.

Can you  please help me to solve this one? 

[JustinDallas · Accepted solution]

I've had a similar problem.  My solution was to use a join table.  If you look at this sample code here, you see that I create a join table, and then I store the property of the Join in said table ('OWNER','CREATOR').  Then, in my set analysis, I can select for the join type.

ServiceRequest:
LOAD *,
[SR#]  AS '%sr_key'
;
LOAD * Inline
[
  'SR#',	'Creator ID',	'Owner ID',	Date
  'sr12eb13',	1,	1,	18-Jan
  'sr12eb14',	2,	1,	18-Jan
  'sr12eb15',	3,	1,	20-Jan
  'sr12eb16',	4,	3,	20-Jan
]
;

DreamTeamMembers:
LOAD *,
[User ID] AS '%user_id_service_key'
;
LOAD * Inline
[
	'User ID',	'User Name',	'Position'
	1,	'John',	'Specialist'
	2,	'Paul',	'Annalist'
	3,	'George',	'Annalist'
	4,	'Tommy',	'Annalist'
]
;

ServiceDreamTeamJoinTable:
LOAD DISTINCT %sr_key, 
[Creator ID] AS '%user_id_service_key',
'CREATOR' AS 'ServiceDreamTeamJoinType'
RESIDENT ServiceRequest
;
LOAD DISTINCT %sr_key, 
[Owner ID] AS '%user_id_service_key',
'OWNER' AS 'ServiceDreamTeamJoinType'
RESIDENT ServiceRequest
;

Count({<ServiceDreamTeamJoinType={'CREATOR'}>} DISTINCT [SR#]) and

Count({<ServiceDreamTeamJoinType={'OWNER'}>} DISTINCT [SR#])

[Leo]

Hello Justin,

I will try this right away, thanks a lot for the quick replay.

[Leo]

Thanks a lot,

Implemented as you describe it and is now working like a charm.

You definitively improve my weekend and my mod.

Cheers,

Leo