Link two tables based on different levels of data

[#1]

Hello,

I am having an issue with linking two tables and have attached an example.

In the Manpower sheet of the spreadsheet I have a simple table that shows the number people on a particular line at a particular hour of the day. In the Transactions sheet of the spreadsheet I have a spreadsheet that shows the number of transactions on a particular line with the key difference being the transaction has a timestamp rather than just going to the hour level.

What I want to do is to show the number of people on a particular line with the associated transactions during a particular hour so that I can calculate transactions per person and any costs connected to this.

As the transaction spreadsheet is at a timestamp level (and the finer detail is required for other aspects of the report) I can not join the tables (as it will add the man power for the hour against all transactions made within the hour), my approach to date has been to create a key field (Date &'-'& Line &'-'& Hour) and to join the two tables and this works ok except when there are no transactions during a particular hour but there is manpower (i.e. the production line still has people on it but the machines may be out of action for a period).

Using the attached spreadsheet as an example my key field for Line 1 at 9 would be '16/12/12-Line 1-9' (from the Manpower table) but as there are no transactions for this period the two tables would not connect. Does anyone know of another approach to link the two datasets or artificially create hours when no transactions have taken place.

I appreciate this is a bit long winded but hopefully you can understand my problem, any help would be great.

Thanks,

Ralph 

[#2]

Hi,

Have you tried something like :

t2:

LOAD Date,

     Timestamp,

     Line,

     Hour,

     Transactions

FROM

Labour.xls

(biff, embedded labels, table is [Transactions$]);

t1:

LOAD Date,

     Line,

     Hour,

     Manpower

FROM

Labour.xls

(biff, embedded labels, table is [Manpower$]);

left join load

          Date,

          Line,

          Hour,

          Sum(Transactions) as Transaction

resident t2

group by

          Date,

          Line,

          Hour;

drop table t2;

[#3 · Accepted solution]

Hi ,

check the attachement..

hope it helps you..

[Gysbert_Wassenaar]

It's probably easiest to just concatenate the two tables. See attached example.