Making a link table

[nickmarlborough]

Hi, 

I am very stuck

Goal - to have a link table where i have ID, Name, Role, Medical ID (as mapped to the ID) and Tech ID (as mapped to the ID)

I have 5 data sets with overlapping fields:

*table 4 is the same as Table 3 however is tech ID instead of medical

*table 5 is the same as Table 2 however is Player Names instead of captain/vice captain, the role is 'player'

********

Table 1 - each row is a unique ID and houses every possible ID.

ID

1

2

3

4

5

Table 2 - this table houses the captains/vice captains of the ID

ID	Name	Role
1	Nick	Captain
1	Josh	Captain
1	Oliver	Vice Captain
2	Nick	Captain
2	Ellie	Vice Captain
3	Jack	Captain

Table 3 - this table houses the ID and Medical ID relevant (each ID holds a variety)

ID	Med ID
1	XXX
1	YYY
1	ZZZ
2	XXX
2	JJJ
3	TTT

[Gabbar]

I don't particularly understand why do you want only link table because your table will by default link correctly on ID.
For table 2 and 5, create a column Role in table 5 with value 'Player' and concatenate these two tables.

[RafaelBarrios]

hi @nickmarlborough 

quick gess, i think you can just use "join" to end with just one table in the data model

table1:
load * inline [
ID
1
2
3
4
5];

left join(table1)
load * inline [
ID,Name,Role
1,Nick,Captain
1,Josh,Captain
1,Oliver,Vice Captain
2,Nick,Captain
2,Ellie,Vice Captain
3,Jack,Captain];

left join(table1)
Load * inline [
ID,Med ID
1,XXX
1,YYY
1,ZZZ
2,XXX
2,JJJ
3,TTT];


left join(table1)
Load * inline [
ID,tech ID
1,XXX
1,YYY
1,ZZZ
2,XXX
2,JJJ
3,TTT];

left join(table1)
Load * inline [
ID,Player Names
1,Rafa
1,Luis
1,Juan
2,Ramon
2,Alex
3,Julio];

this will be the resulting table

you could also do a simple load and just build the visualization table

table1:
load * inline [
ID
1
2
3
4
5];

table2:
load * inline [
whatever];

table3:
Load * inline [
whatever];

table4:
Load * inline [
whatever];

table5:
Load * inline [
ID,Player Names
1,Rafa
1,Luis
1,Juan
2,Ramon
2,Alex
3,Julio];

ending with this model

and building the table

but I'm pretty sure this is not what you need, if so, paste an excel with the results you expect

Hope this helps.

Best,

help users find answers! Don't forget to mark a solution that worked for you & to smash the like button! 

[nickmarlborough]

its probably the first example you gave where they all are in the same table. 

The problem i have which for the life of me i cant figure out....

I am trying to get the count of medical IDs that are mapped to IDs where the field 'Red' (located in Table 1) is 'Yes' - however the number it provides is far less that what it should be. For some reason... the Med IDs are not all appearing in the link table, and have no clue why.