How to merge two tables?

posywang — Sun, 13 Aug 2017 20:56:34 GMT

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?

Re: How to merge two tables?

petter — Sun, 13 Aug 2017 21:19:56 GMT

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

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...

topic Re: How to merge two tables? in QlikView

How to merge two tables?

Re: How to merge two tables?