Aggregate two tables onto one granulity

[joslaz]

Hello everybody,


I have two tables in the following format:

tblSkills:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]ID[/TD]
[TD]Function[/TD]
[TD]Assembly[/TD]
[TD]Drilling[/TD]
[TD]Polishing[/TD]
[TD]Package[/TD]
[/TR]
[TR]
[TD]Jeremy[/TD]
[TD]001[/TD]
[TD]Operator[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Rodger[/TD]
[TD]002[/TD]
[TD]Operator[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]


tblPlanning:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]ID[/TD]
[TD]State[/TD]
[/TR]
[TR]
[TD]01.01.2019[/TD]
[TD]001[/TD]
[TD]Assembly[/TD]
[/TR]
[TR]
[TD]01.01.2019[/TD]
[TD]001[/TD]
[TD]Drilling[/TD]
[/TR]
</tbody>[/TABLE]




It can be seen that the first table is partly a cross table.
The other table is a classic list.


Both can be linked using a calendar table and a master data list (name, ID).
Now I have to somehow connect the abilities with each other, in order to be able to carry out appropriate evaluations with the help of a pivot table.
Does anyone of you have any idea which structure would be best for this?


Best regards

 

[sandy666]

I assume you want to join these two tables into one so via PQ

tblSkills
- select first three columns and Unpivot Other Columns
- change Attribute to State

merge tblSkills and tblPlanning by ID and State
- Expand

create PivotTable from Merge-Table

 

[citizenbh]

You will have a problem with the double ID number in the tblPlanning

 

[sandy666]

I don't think so but we will see

 

[joslaz]

Thanks for the replies!

I dont want a Merge of both tables in one. I am looking for a data modell.
So, I think a need a reference table with unique values for ID, the skills and the dates.
The challange is to determine a referance table for the skills.
How would you manage that?

 

[sandy666]

I'd like to see expected result