10 Replies Latest reply: Aug 28, 2012 5:53 AM by Thomas Duroyon RSS

    How to use loaded data under a different field name without loading the data again

      Hi,

       

      I struggle to find a correct title...

       

      I have sales in different files : Actuals 2011, Plan (budget) 2012, Actuals 2012, etc.

      The load is as follow for all files:

       

      Directory;

      CrossTable(Month, Sales2012Plan, 4)

      LOAD COUNTRY,

           [PRODUCT NAME],

           Year,

           [Sales source],

           Jan,

           Feb,

           Mar,

           Apr,

           May,

           Jun,

           Jul,

           Aug,

           Sep,

           Oct,

           Nov,

           Dec

      FROM

      [APAC Sales Report - 2012 Plan.xls]

      (biff, embedded labels, table is Database$);

       

       

      what differs are the parts in red.

      I have loaded those files to allow me to do graph 1 (see attachment)

       

      Now as I wanted to do the graph 2, I could not find a way to do it with 2011 months followed by 2012 months.

      So I reloaded all the exact same tables with the following changes in blue

      for budget data :

       

      Directory;

      CrossTable(Monthactual, salesplan, 2)

      LOAD COUNTRY,

           [PRODUCT NAME],

           Jan as [Jan-12],

           Feb as [Feb-12],

           Mar as [Mar-12],

           Apr as [Apr-12],

           May as [May-12],

           Jun as [Jun-12],

           Jul as [Jul-12],

           Aug as [Aug-12],

           Sep as [Sep-12],

           Oct as [Oct-12],

           Nov as [Nov-12],

           Dec as [Dec-12]

      FROM

      [APAC Sales Report - 2012 Plan.xls]

      (biff, embedded labels, table is Database$, filters(

      Remove(Col, Pos(Top, 3)),

      Remove(Col, Pos(Top, 3))

      ));

       

      For actuals:

      Directory;

      CrossTable(Monthactual, salesactual, 2)

      LOAD COUNTRY,

           [PRODUCT NAME],

           Jan as [Jan-12],

           Feb as [Feb-12],

           Mar as [Mar-12],

           Apr as [Apr-12],

           May as [May-12],

           Jun as [Jun-12],

           Jul as [Jul-12],

           Aug as [Aug-12],

           Sep as [Sep-12],

           Oct as [Oct-12],

           Nov as [Nov-12],

           Dec as [Dec-12]

      FROM

      [APAC Sales Report - 2012 LBE.xls]

      (biff, embedded labels, table is Database$, filters(

      Remove(Col, Pos(Top, 3)),

      Remove(Col, Pos(Top, 3))

      ));

       

       

      I am convinced that there must be a much simpler way (as I have 12000 lines per table and I am loading now 2x 6 tables with some changes + crosstables) it takes for ever.

       

      Please help.

      Thnks