Link Table

[nickmarlborough]

Hi, 

I have data sets that i am trying to join, but a synthetic key is getting created and i have been told a 'Link Table' is needed. 

I have x3 data sets that i need to join. 

Table 1 = Captain and Vice Captain

ID	Initials	Role
123	NM	Captain
456	JK	Captain
789	OH	Vice Captain
906	GH	Captain

Table 2 = ID and Players

ID	Initials
123	NM
123	HJ
123	UI
906	KL
906	AS
818	KL

Table 3 = Player Info

Initials	City
NM	london
HJ	new york
UI	london
KL	london
AS	singapore
KL	berlin
FG	stockholm

What i am trying to get is a costructed data set that when a captain is selected it brings in scope the ID that the person is captain for as well as the captains information (like city). In addition i would like it that when a regular player is selected it brings the ID for which that player belongs and if he/she captains/vice captains a team, the ID that they captain. In red - shows that there are players not mapped to a team but need to be included, so lets assume there is a scenario where FG is not in a team but somehow captains them. I want that ID to be filtered. 

Hope this is clear. High level, table one shows all possible teams (ID) and the captains and vice captains. Table 2 shows all the players and the teams they are mapped to and table 3 shows every possible players (some mapped and some not mapped) - i want to have my dashboard set up in a way that it is linked on the ID and also has a link for the initials (so the person)

[Braveen]

Can you share the desired output? May be it is me, but I didn't fully understand what is it that you want to achieve. So, if you share instance based examples/scenario, that would be helpful.

[JonnyPoole]

I think you just OUTER JOIN the tables together in the load script. And I would recommend populating some other handy dimensions into the data to better support the analytics you plan to do. 

Since the default 'JOIN' in Qlik is an outer join, I just say 'JOIN' below, but feel free to replace with OUTER JOIN if that is more intuitive.  Joins will be made on all fields that have the same case senstive names. So in this case we join on multiple fields in the LOADs.  This will create 1 table in your data model.  You will see I've added 2 additional fields in there for your analytics. Entirely optional. 

TEMP:

LOAD

*

FROM <CAPTAINandVICECAPTAIN>;

JOIN (TEMP)

LOAD

*,

'Yes' as MappedToTeam

FROM IDandPlayers;

JOIN (TEMP)

LOAD

*

FROM <PlayerInfo>

//Consider the following:

DATA:

noconcatenate

LOAD

ID,

Initials,

City,

If( match(Role,'Captain','Vice Captain'),Role,'Player') as Role,

If( match(MappedToTeam,'Yes'),MappedToTeam,'No') as MappedToTeam

RESIDENT TEMP;

DROP TABLE TEMP;