Different column in a table point to a single ID column

[mansoorsheraz]

Hi,

I am, making an app where I have the userID but that is being used in another table in three other columns. How can I make the load script so that selecting an employee can let me know how many projects he is supervising and how many he is reviewing etc. Below attached is the sample data model. Any help is appreciated. (AssignedTo, Supervisor, Reviewer all have the UserID value in them)

The End Result should be that when a filter on the UserName is applied, the KPI's for Assigned, Supervised, Reviewed should have the respective value in them.

e.g. if in the filter for Users "John" is selected  then the KPI's should display:

Assigned     Supervised    Reviwed
     0                        2                       0

Regards.

[sidhiq91]

@mansoorsheraz  Please use the below code in the back end script first:

NoConcatenate
Temp1:
Load * inline [
JobID, Jobname, Assigned to, Supervisor, Reviewer
1,Apply Cement,1,2,4
2,Plumbing works,3,2,5
];

Temp2:
crosstable(UserID,Values)
load JobID,
//Jobname,
[Assigned to],
[Supervisor],
[Reviewer]
Resident Temp1;

Drop table Temp1;

NoConcatenate
Temp3:
Load JobID,
Values as UserID,
UserID as JOBTitle
Resident Temp2;
Drop table Temp2;

Left join (Temp3)
Load * inline [
UserID, UserName
1,Smith
2,John
3,Michael
4,Tom,
5,Laurel
6,Johnson
];

Exit Script;

In the Front End you can use something as below for Expression:

Supervised: Count({<JOBTitle={'Supervisor'}>} UserID)

Assigned: Count({<JOBTitle={'Assigned to'}>} UserID)

Reviewed: Count({<JOBTitle={'Reviewer'}>}UserID)

If this resolves your issue, please like and accept it as a solution.

[anat]

Temp1:
CrossTable(deg, depname,2)
Load * inline [
JobID, Jobname, Assigned to, Supervisor, Reviewer
1,Apply Cement,1,2,4
2,Plumbing works,3,2,5
];

LOAD UserID as depname, UserName

;
Load * inline [
UserID, UserName
1,Smith
2,John
3,Michael
4,Tom,
5,Laurel
6,Johnson
];

[mansoorsheraz]

@sidhiq91 @anat Thank you both for your solutions. But the issue that I am, facing is far more completed. These three columns AssignedTo, Supervisor, Reviewer , they come in other tables too e.g. Audits, Surveys, Tasks etc with the base table being the same i.e. tbl_users. How can I have them all coming up here? If I start to add in the the ID's from Audits, Surveys, etc synthetic keys start to come up as well. Can you please guide me?

Regards.