Developing analysis from two separate tables

Anonymous — Fri, 21 Aug 2015 02:57:37 GMT

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

Table 1

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?

Re: Developing analysis from two separate tables

Sat, 22 Aug 2015 03:12:57 GMT

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