How to merge two tables?

[posywang]

We have two systems for forecast (please don't ask me why... ), so I have Sys1Allocation in Table 1 and Sys2Allocation in Table 2. Allocations may be slightly different in two systems because of rounding, slightly different calendar being used, etc. System 1 is our system of record but system 2 contains some field (Hobby as an example below) that system 1 doesn't have but important to be used in our reporting. We get two flat files from system 1 and 2 everyday. Now the goal is to merge the two tables below and generate a new table. Is it a possible mission? Please let me know if there's any question. Your help is greatly appreciated!

Blue percentage column doesn't exist in table. It is calculated based on person's sys2allocation. Not sure if this can be done easily. Can this be done on the fly in scripting?

[petter]

Maybe something like this:

T1:
LOAD * INLINE [
Area, Resource, Sys1Allocation
Finance, Amy,1
Accounting, Jack,1
IT, Mary,1
];

JOIN

LOAD * INLINE [
Area, Resource, Hobby, Sys2Allocation
Finance, Amy, Fishing, 1.1
Accounting, Jack, Game, 0.7
Accounting, Jack, Movie, 0.35
IT, Mary, Dancing, 0.45
IT, Mary, Movie, 0.65
];

JOIN

LOAD
Area, Resource, Sum(Sys2Allocation) AS Tot
RESIDENT
    T1
GROUP BY
    Area, Resource;

T2:
LOAD
  *,
  Sys2Allocation / Tot AS Sys1AllocationPercentage
RESIDENT
  T1;

DROP TABLE T1;  

However you might have to modify the calculation so it involves Sys1Allocation too... didn't understand from your explanation how it should be involved...