Ok, I'm new to Qlik Sense and have been trying to figure out how to properly link my technician table to multiple other tables such as the Cases and Sessions I have listed below. The data to start was pretty messy so I've used ApplyMap() to the cases and sessions table to use the TechnicianID rather than their login, email address, or name. So, I think I'm close but I haven't found the right solution to make this work. I also have two more tables that contain createdby and other similar fields that I need to link to.
Technicians:
TechnicianID | TechnicianLogin | TechnicianName |
|---|
| 123345 | login1 | Smith, John |
| 324546 | login2 | Smith, Jane |
Cases:
| CaseID | CreatedBy | ModifiedBy | CurrentOwner |
|---|
| 1 | 123345 | 324546 | 324546 |
| 2 | 324546 | 123345 | 123345 |
Sessions:
| SessionID | CreatedBy | ModifiedBy |
|---|
| 4 | 324546 | 123345 |
| 5 | 123345 | 324546 |
Here is an abbreviated sample of the load script I'm using for the Cases table (the Cases table initially had the technicianlogin in createdby, modifiedby and currentowner):
Technicians:
LOAD
TechnicianID,
TechnicianName,
TechnicianLogin,
TechnicianEmail,
Site,
Coach,
LOB
FROM ...;
TechniciansByLogin:
Mapping LOAD
TechnicianLogin,
TechnicianID
Resident Technicians Order By TechnicianID;
Cases:
LOAD
...
ApplyMap('TechniciansByLogin',[Created by],'Undefined') as CreatedBy,
ApplyMap('TechniciansByLogin',[Modified by],'Undefined') as ModifiedBy,
ApplyMap('TechniciansByLogin',[Current Owner],'Undefined') as CurrentOwner
FROM ...
Any help is appreciated!
