Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
NEW webinar Dec. 7th: 2023 Outlook, A Pivotal Year for Data Integration SIGN ME UP!
cancel
Showing results for 
Search instead for 
Did you mean: 
Leo
Partner - Contributor II
Partner - Contributor II

Counting Created and Owned for a Particular User

Hello guys,

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

SR#Creator IDOwner IDDate
sr12eb131118-Jan
sr12eb142118-Jan
sr12eb153120-Jan
sr12eb164320-Jan

 

And a second table like this one Team members:

User IDUser NamePosition
1JohnSpecialist
2PaulAnnalist
3GeorgeAnnalist
4TommyAnnalist

 

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

Filter User NameJohn

 

DateCreatedOwned
18-Jan12
20-Jan01

 

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? 

1 Solution

Accepted Solutions
JustinDallas
Specialist III
Specialist III

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#])

MitchIce.png

View solution in original post

3 Replies
JustinDallas
Specialist III
Specialist III

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#])

MitchIce.png

Leo
Partner - Contributor II
Partner - Contributor II
Author

Hello Justin,

I will try this right away, thanks a lot for the quick replay.
Leo
Partner - Contributor II
Partner - Contributor II
Author

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