One field exists many times in another table - how to link the tables with this field?

[#1]

Hi all!

I have two tables: Staff and Loggers

Staff table:

StaffKey	StaffName
AB01	Joe
CD02	Michelle
EF03	Mark
GH04	Tony
IJ05	Ben

Loggers:

LogNo	CreatedBy	UpdatedBy	CompletedBy
1	AB01	IJ05	GH04
2	CD02	EF03	AB01
3	IJ05	EF03	CD02

How can I link StaffKey values to the CreatedBy, UpdatedBy, CompletedBy fields in the Loggers table please?

[jonasheisterkam]

You need a link table.

LinkTable:

Load

LogNo,

CreateBy as StaffKey

resident Loggers;

concatenate

Load

LogNo,

UpdatedBy as StaffKey

resident Loggers;

concatenate

Load

LogNo,

CompletedBy as StaffKey

resident Loggers;

[consenit]

Hi there.

Use a Crosstable load:

Loggers:

CrossTable(CreatedBy, StaffKey)

LOAD LogNo,

     CreatedBy,

     UpdatedBy,

     CompletedBy

FROM

Tables.xlsx

(ooxml, embedded labels, table is Table1);

Staff:

LOAD StaffKey,

     StaffName

FROM

Tables.xlsx

(ooxml, embedded labels, table is Table2);

Kind regards,

Ernesto.

[#4]

Hi Ernesto,

How would the Crosstable function work?

In the Loggers table my common fields are layed out between other fields such as

CreatedBy, Field1, Field2, Field3, UpdatedBy, field4, field5, CompletedBy, field6... etc.

How can I get the Crossfield function to cover all three common fields between those fields?

EDIT: I can change the order of fields in LOAD if required.

[#5]

Hi Jonas,

What if I have more than the LogNo field? E.g. Status, ProblemID etc.

Do I need to put each in the Link table?

[jonasheisterkam]

You need a key from the Loggers table. If LogNo is not a key you can build one from CreateBy, UpdatedBy and CompletedBy. You can use funktions like hash or autonumberhash or to minimize the link table you can use a order in the param list from hash. => a,b,c = b,c,a = ... but this is only performance fu you have the choice to create a faster data model or a faster script.

But more importent is to check if you need a datamodel with a link table(or join) then you have the fields CreateBy, UpdatedBy and CompletedBy. Or you need it like the result of a cross table one field with the operation the other with the name.

Depending on your calculations in the gui and the data properties it effects the dashboard performance. Try to find the best option for a easy gui creation.

[johnca · Accepted solution]

Hi Test Test,

I would map that in;

Staff_Map:
Mapping Load

     StaffKey,

     StaffName

From StaffTable;

Loggers:

Load

     LogNo,

     ApplyMap('Staff_Map',CreatedBy,'Undefined') as CreatedBy,

     ApplyMap('Staff_Map',UpdatedBy,'Undefined') as UpdatedBy,

     ApplyMap('Staff_Map',CompletedBy,'Undefined') as CompletedBy

From Loggers;    

The additional ,'Undefined' will return 'Undefined' as the name when a value isn't found in the map table.

HTH,

John

[#8]

Thank you John!