Developing analysis from two separate tables

[Anonymous]

Hello everyone.  Newbie here.  Have a question about developing an analysis from two separate tables.

Table 1

Part ID	Scenario	Cost
1	Actual	A
1	Forecast	B

Table 2

Part ID	Scenario	Variance
1	Actual vs Forecast	C

The analysis I want to show is:

Part ID	Actual Cost	Forecast Cost	Variance
1	A	B	C

Note that the variance is not a simple calculation between A and B so a manual calc would not work.

Any ideas?

[#2]

I would suggest you to concatenate these two tables. Script below

MAP:

Mapping LOAD * INLINE [

    SCENARIO, FLAG

    Actual, 1

    Forecast, 2

    Actual vs Forecast, 3

];

Tab1:

LOAD [Part ID],

     Scenario,

     ApplyMap('MAP',Scenario) as FLAG,

     Cost as Value

FROM

[177402.xlsx]

(ooxml, embedded labels, table is Table1);

Tab2:

Concatenate (Tab1)

LOAD [Part ID],

     Scenario,

     ApplyMap('MAP',Scenario) as FLAG,

     Variance as Value

FROM

[177402.xlsx]

(ooxml, embedded labels, table is Table2);

And expression definitions as below

Actual Cost - Sum({<FLAG={1}>} Value)

Forecast Cost - Sum({<FLAG={2}>} Value)

Variance - Sum({<FLAG={3}>} Value)

Regards,

KKR