1 Reply Latest reply: Aug 21, 2015 11:12 PM by Kranthikumar Miryala RSS

    Developing analysis from two separate tables

    Mark Rudolphi

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

       

      Table 1

      Part IDScenarioCost
      1ActualA
      1ForecastB

      Table 2

      Part IDScenarioVariance
      1Actual vs ForecastC

       

       

      The analysis I want to show is:

       

      Part IDActual CostForecast CostVariance
      1ABC

      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
          Kranthikumar Miryala

          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