7 Replies Latest reply: Sep 10, 2015 3:25 AM by Amit Saini RSS

    Mapping help???

    Amit Saini

      Hi Folks,

       

      Below is my issue.

       

      Reporting Unit_Name is not showing any related data for Platform_Name , CustomerHier01_Name and Vehicle_Name

      Script:

       

      UNQUALIFY *;

       

      //start mapping load statements from here if any.

       

       

      CustomerHier01_Mapping:

      Mapping

      LOAD

           KUNNR as CustomerHier01,    

           NAME1

          

      FROM

      [D:\qvprod\qvd\SAP\KTX_100\KNA1.qvd]

      (qvd);

       

      CustomerHier02_Mapping:

      Mapping

      LOAD

           KUNNR as CustomerHier02,    

           NAME1

          

      FROM

      [D:\qvprod\qvd\SAP\KTX_100\KNA1.qvd]

      (qvd);

       

      Continent_Mapping:

      Mapping LOAD

       

      WW001 as Continent, 

           BEZEK

      FROM

      [D:\qvprod\qvd\SAP\KTX_100\T25A1.qvd]

      (qvd)

      WHERE SPRAS = 'E';

       

       

      [PH - Automotive Y/N_Mapping]:

      Mapping LOAD  WWPH4 as [PH - Automotive Y/N],

           BEZEK

      FROM

      [D:\qvprod\qvd\SAP\KTX_100\T25A3.qvd]

      (qvd)

      WHERE SPRAS = 'E';

       

      [PH - Type of Product_Mapping]:

      Mapping LOAD

      WWPH5 as [PH - Type of Product],

           BEZEK

      FROM

      [D:\qvprod\qvd\SAP\KTX_100\T25A4.qvd]

      (qvd)

      WHERE SPRAS = 'E';

       

       

      [PH -Specific Product_Mapping]:

      Mapping LOAD

       

          WWPH6 as [PH -Specific Product],

           BEZEK

      FROM

      [D:\qvprod\qvd\SAP\KTX_100\T25A5.qvd]

      (qvd)

      WHERE SPRAS = 'E';

       

       

      Platform_Mapping:

      Mapping LOAD

       

      WW004 as Platform,

           BEZEK

      FROM

      [D:\qvprod\qvd\SAP\KTX_100\T25A7.qvd]

      (qvd)

      WHERE SPRAS = 'E';

       

       

      Vehicle_Mapping:

      Mapping LOAD

       

      WWM10 as Vehicle,

           BEZEK

      FROM

      [D:\qvprod\qvd\SAP\KTX_100\T25A9.qvd]

      (qvd)

      WHERE SPRAS = 'E';

       

       

      [Reporting Unit_Mapping]:

      Mapping LOAD

       

      WW006 as [Reporting Unit],

           BEZEK

      FROM

      [D:\qvprod\qvd\SAP\KTX_100\T25B0.qvd]

      (qvd)

      WHERE SPRAS = 'E';

       

      FOR EACH vPlant in '000', '002','003','004','008','011','022','400'

       

      set vTab = 'CE42$(vPlant)';

      CE42:

      //CONCATENATE(CE42)

      LOAD

       

      PAOBJNR &'-'& $(vPlant) as %PartnerObjectNumber,

      PAOBJNR as PAOBJNR_42,

       

      'CE3242' as Table_Name,

       

      //PERBL    as [Period block/year],

      //REC_WAERS    as [Record Currency],

       

      KMHI01    as CustomerHier01,

      KMHI02    as CustomerHier02,

      WW001    as Continent,

      WW006    as [Reporting Unit],

      WW004    as Platform,

      WWM10    as Vehicle,

      WWPH4    as [PH - Automotive Y/N],

      WWPH5    as [PH - Type of Product],

      WWPH6    as [PH -Specific Product]

      from $(vTab).qvd(qvd)

      WHERE NOT ISNULL(PAOBJNR) ;

      NEXT;

       

      FOR EACH vPlant in '000','002','003','004','008','011','022','400'

       

      set vTab = 'CE32$(vPlant)';

      CE32:

      //CONCATENATE(CE32)

      // JOIN

      LOAD

       

       

       

      PAOBJNR &'-'& $(vPlant) as %PartnerObjectNumber,

      PAOBJNR as PAOBJNR_32,

      IF(PLIKZ= 0 ,'Actual/FC','Budget') as Type,

      'CE3242' as Table_Name,

       

      PERBL    as [Period block/year],

      REC_WAERS    as [Record Currency],

       

          PLIKZ         as AOP_Data_Flag,

      VVS01001    as [Sales Price],

      VVS02001    as [Pump price change],

      VVS03001    as [Raw Mat price change],

      VVS04001    as [Other comp. price ch],

      VVS05001    as [Savings],

      VVS12001    as [Price Retrobilling],

      VV001001    as [Transp to suppl plnt],

      VV002001    as [Transp to customer],

      VV003001    as [Final Assembly rev.],

      VV004001    as [Sequenzing revenue],

      VVS13001    as [Price Changes],

      VVS10001    as [Amortisation intern],

      VVS14001    as [Order/Project Sales],

      VVS06001    as [Cash Discount calc.],

      VVS11001    as [Rebates],

      VVS07001    as [Cash Discount actual],

       

      VVM15001    as [ML Freight-in],

      VVM01001    as [ML Packaging costs],

      VVC20001    as [Sequenzing costs],

      VVC21001    as [Freight-out],

      VVM02001    as [ML Raw materials],

      VVM03001    as [ML Pumps],

      VVM04001    as [ML Fluor/Nitrogen],

      VVM05001    as [ML oth. comp.- purch],

      VVC07001    as [Internal Deliv. cost],

      VVC30001    as [CoGS Mat/COMP/Pumps],

      VVM09001    as [ML Direct Labor -var],

      VVM10001    as [ML Contract labor],

      VVM12001    as [ML mach. cost - var],

       

      VVC01001    as [Freight-in],

      VVC02001    as [Packaging costs],

      //VVC20001    as [Sequenzing costs],

      //VVC21001    as [Freight-out],

      ROHST001    as [Raw materials],

      VVC04001    as [Pumps],

      VVC05001    as [Fluor/Nitrogen],

      VVC06001    as [Other comp.- purchas],

      //VVC07001    as [Internal Deliv. cost],

      //VVC30001    as [CoGS Mat/COMP/Pumps],

      VVC08001    as [Direct Labor (var.)],

      VVC09001    as [Contract labor],

      VVC10001    as [Machine cost (var.)],

       

      IF( PLIKZ = 0,

      VVS01001

      +VVS02001

      +VVS03001

      +VVS04001

      +VVS05001

      +VVS12001

      +VV001001

      +VV002001

      +VV003001

      +VV004001

      +VVS13001

      +VVS10001

      +VVS14001

      -VVS06001

      +VVS11001

      +VVS07001,0)

        as ACTUAL_SALES,

       

      IF( PLIKZ = 1,

      VVS01001

      +VVS02001

      +VVS03001

      +VVS04001

      +VVS05001

      +VVS12001

      +VV001001

      +VV002001

      +VV003001

      +VV004001

      +VVS13001

      +VVS10001

      +VVS14001

      -VVS06001

      +VVS11001

      +VVS07001,0)

        as PLAN_SALES,

       

      IF( PLIKZ = 0,

      VVS01001

      +VVS02001

      +VVS03001

      +VVS04001

      +VVS05001

      +VVS12001

      +VV001001

      +VV002001

      +VV003001

      +VV004001

      +VVS13001

      +VVS10001

      +VVS14001

      -VVS06001

      +VVS11001

      +VVS07001

      -VVM15001

      - VVM01001

      - VVC20001

      - VVC21001

      - VVM02001

      - VVM03001

      - VVM04001

      - VVM05001

      - VVC07001

      //- VVC30001

      - VVM09001

      - VVM10001

      - VVM12001,0)

        as ACTUAL_MARGIN,

       

        IF( PLIKZ = 1,

      VVS01001

      +VVS02001

      +VVS03001

      +VVS04001

      +VVS05001

      +VVS12001

      +VV001001

      +VV002001

      +VV003001

      +VV004001

      +VVS13001

      +VVS10001

      +VVS14001

      -VVS06001

      +VVS11001

      +VVS07001

      -VVC01001

      -VVC02001

      -VVC20001

      -VVC21001

      -ROHST001

      -VVC04001

      -VVC05001

      -VVC06001

      -VVC07001

      -VVC30001

      -VVC08001

      -VVC09001

      -VVC10001)

       

        as PLAN_MARGIN,

       

      IF( PLIKZ = 0,

        ( ( 

      VVS01001

      +VVS02001

      +VVS03001

      +VVS04001

      +VVS05001

      +VVS12001

      +VV001001

      +VV002001

      +VV003001

      +VV004001

      +VVS13001

      +VVS10001

      +VVS14001

      -VVS06001

      +VVS11001

      +VVS07001

      - VVM15001

      - VVM01001

      - VVC20001

      - VVC21001

      - VVM02001

      - VVM03001

      - VVM04001

      - VVM05001

      - VVC07001

      //- VVC30001

      - VVM09001

      - VVM10001

      - VVM12001) /

      ( VVS01001

      +VVS02001

      +VVS03001

      +VVS04001

      +VVS05001

      +VVS12001

      +VV001001

      +VV002001

      +VV003001

      +VV004001

      +VVS13001

      +VVS10001

      +VVS14001

      -VVS06001

      +VVS11001

      +VVS07001)) ,0)

        as [Margin ACTUAL (% of Sales)],

       

      IF( PLIKZ = 1,

        ( ( 

      VVS01001

      +VVS02001

      +VVS03001

      +VVS04001

      +VVS05001

      +VVS12001

      +VV001001

      +VV002001

      +VV003001

      +VV004001

      +VVS13001

      +VVS10001

      +VVS14001

      -VVS06001

      +VVS11001

      +VVS07001

      -VVC01001

      -VVC02001

      -VVC20001

      -VVC21001

      -ROHST001

      -VVC04001

      -VVC05001

      -VVC06001

      -VVC07001

      -VVC30001

      -VVC08001

      -VVC09001

      -VVC10001

      ) /

      ( VVS01001

      +VVS02001

      +VVS03001

      +VVS04001

      +VVS05001

      +VVS12001

      +VV001001

      +VV002001

      +VV003001

      +VV004001

      +VVS13001

      +VVS10001

      +VVS14001

      -VVS06001

      +VVS11001

      +VVS07001)) ,0)

        as [Margin Plan (% of Sales)]

       

       

      from $(vTab).qvd(qvd)

      WHERE NOT ISNULL(PAOBJNR) ;

       

      NEXT;

      //exit script;

      //DROP FIELD dummy_42;

       

      Join (CE42)

      LOAD *

      RESIDENT CE32;

       

      drop table CE32;

       

      CE_All_TEMP:

      LOAD

      //[Period block/year],

      //[Record Currency],

      //AOP_Data_Flag,

      //SUM(ACTUAL_SALES) as ACTUAL_SALES,

      //SUM(PLAN_SALES) as PLAN_SALES,

      //SUM(ACTUAL_MARGIN) as ACTUAL_MARGIN,

      //SUM(PLAN_MARGIN) as PLAN_MARGIN,

      //SUM([Margin ACTUAL (% of Sales)]) as [Margin ACTUAL (% of Sales)],

      //SUM ([Margin Plan (% of Sales)]) as [Margin Plan (% of Sales)],

       

      *,

      [Record Currency]&'-'& NUM#(right([Period block/year],2),'#') as %XRATE_KEY1,

      [Record Currency]&'-'& NUM#(right(left([Period block/year],4),2),'#') as %XRATE_KEY2,

      [Record Currency]&'-'& NUM#(right([Period block/year],2),'#') as %XRATE_KEY1_SAP,

      [Record Currency]&'-'& NUM#(right(left([Period block/year],4),2),'#') as %XRATE_KEY2_SAP,

      CustomerHier01 &'-'& APPLYMAP('CustomerHier01_Mapping',CustomerHier01) as CustomerHier01_Name,

      CustomerHier02 &'-'& APPLYMAP('CustomerHier02_Mapping',CustomerHier02) as CustomerHier02_Name,

      Continent &'-'& APPLYMAP('Continent_Mapping',Continent) as Continent_Name,

      [PH - Automotive Y/N] &'-'& APPLYMAP('PH - Automotive Y/N_Mapping',[PH - Automotive Y/N]) as [PH - Automotive Y/N_Name],

      [PH - Type of Product] &'-'& APPLYMAP('PH - Type of Product_Mapping',[PH - Type of Product]) as [PH - Type of Product_Name],

      [PH -Specific Product] &'-'& APPLYMAP('PH -Specific Product_Mapping',[PH -Specific Product]) as [PH -Specific Product_Name],

      Platform &'-'& APPLYMAP('Platform_Mapping',Platform) as Platform_Name,

      [Reporting Unit] &'-'& APPLYMAP('Reporting Unit_Mapping',[Reporting Unit]) as [Reporting Unit_Name],

      Vehicle &'-'& APPLYMAP('Vehicle_Mapping',Vehicle) as [Vehicle_Name]

       

      RESIDENT CE42

      ;

       

      DROP TABLE CE42;

      DROP FIELD CustomerHier01, CustomerHier02, Continent ,[PH - Automotive Y/N] ,[PH - Type of Product] ,[PH -Specific Product],Platform ,[Reporting Unit] ,Vehicle;

       

       

      //QUALIFY *;

       

      CE_All:

      //A:

      LOAD

      CustomerHier01_Name,

      CustomerHier02_Name,

      //'CustomerHier' as Dimention,

      %XRATE_KEY1,

      %XRATE_KEY2,

      CustomerHier01_Name & '-' & CustomerHier02_Name as Dimention,

      'CustomerHier01_Name - CustomerHier02_Name' as Dimention_Type,

       

      //[Period block/year],

      //[Record Currency],

      //AOP_Data_Flag,

      SUM(ACTUAL_SALES) as ACTUAL_SALES,

      SUM(PLAN_SALES) as PLAN_SALES,

      SUM(ACTUAL_MARGIN) as ACTUAL_MARGIN,

      SUM(PLAN_MARGIN) as PLAN_MARGIN,

      SUM([Margin ACTUAL (% of Sales)]) as [Margin ACTUAL (% of Sales)],

      SUM ([Margin Plan (% of Sales)]) as [Margin Plan (% of Sales)]

       

      RESIDENT CE_All_TEMP

      GROUP BY CustomerHier01_Name,CustomerHier02_Name,%XRATE_KEY2, %XRATE_KEY1;

       

      CONCATENATE (CE_All)

      LOAD

      Continent_Name,

      [Reporting Unit_Name],

      %XRATE_KEY1,

      %XRATE_KEY2,

      Continent_Name &'-'& [Reporting Unit_Name] as Dimention,

      'Continent_Name - Reporting Unit_Name' as Dimention_Type,

      //[Period block/year],

      //[Record Currency],

      //AOP_Data_Flag,

      SUM(ACTUAL_SALES) as ACTUAL_SALES,

      SUM(PLAN_SALES) as PLAN_SALES,

      SUM(ACTUAL_MARGIN) as ACTUAL_MARGIN,

      SUM(PLAN_MARGIN) as PLAN_MARGIN,

      SUM([Margin ACTUAL (% of Sales)]) as [Margin ACTUAL (% of Sales)],

      SUM ([Margin Plan (% of Sales)]) as [Margin Plan (% of Sales)]

       

      RESIDENT CE_All_TEMP

      GROUP BY Continent_Name,[Reporting Unit_Name],%XRATE_KEY2, %XRATE_KEY1;

       

      CONCATENATE (CE_All)

      LOAD

      [PH - Automotive Y/N_Name],

      [PH - Type of Product_Name],

      [PH -Specific Product_Name],

      %XRATE_KEY1,

      %XRATE_KEY2,

      [PH - Automotive Y/N_Name] & '-' & [PH - Type of Product_Name] & '-' & [PH -Specific Product_Name] as Dimention,

      'PH - Automotive Y/N_Name - PH - Type of Product_Name - PH -Specific Product_Name' as Dimention_Type,

      //[Period block/year],

      //[Record Currency],

      //AOP_Data_Flag,

      SUM(ACTUAL_SALES) as ACTUAL_SALES,

      SUM(PLAN_SALES) as PLAN_SALES,

      SUM(ACTUAL_MARGIN) as ACTUAL_MARGIN,

      SUM(PLAN_MARGIN) as PLAN_MARGIN,

      SUM([Margin ACTUAL (% of Sales)]) as [Margin ACTUAL (% of Sales)],

      SUM ([Margin Plan (% of Sales)]) as [Margin Plan (% of Sales)]

       

      RESIDENT CE_All_TEMP

      GROUP BY [PH - Automotive Y/N_Name],

      [PH - Type of Product_Name],

      [PH -Specific Product_Name],%XRATE_KEY2, %XRATE_KEY1;

       

       

      CONCATENATE (CE_All)

      LOAD

      Platform_Name,

      %XRATE_KEY1,

      %XRATE_KEY2,

      Platform_Name as Dimention,

      'Platform_Name' as Dimention_Type,

      //[Period block/year],

      //[Record Currency],

      //AOP_Data_Flag,

      SUM(ACTUAL_SALES) as ACTUAL_SALES,

      SUM(PLAN_SALES) as PLAN_SALES,

      SUM(ACTUAL_MARGIN) as ACTUAL_MARGIN,

      SUM(PLAN_MARGIN) as PLAN_MARGIN,

      SUM([Margin ACTUAL (% of Sales)]) as [Margin ACTUAL (% of Sales)],

      SUM ([Margin Plan (% of Sales)]) as [Margin Plan (% of Sales)]

       

      RESIDENT CE_All_TEMP

      GROUP BY Platform_Name,%XRATE_KEY2, %XRATE_KEY1;

       

      CONCATENATE (CE_All)

      LOAD

      [Vehicle_Name],

      %XRATE_KEY1,

      %XRATE_KEY2,

      [Vehicle_Name] as Dimention,

      'Vehicle_Name' as Dimention_Type,

      //[Period block/year],

      //[Record Currency],

      //AOP_Data_Flag,

      SUM(ACTUAL_SALES) as ACTUAL_SALES,

      SUM(PLAN_SALES) as PLAN_SALES,

      SUM(ACTUAL_MARGIN) as ACTUAL_MARGIN,

      SUM(PLAN_MARGIN) as PLAN_MARGIN,

      SUM([Margin ACTUAL (% of Sales)]) as [Margin ACTUAL (% of Sales)],

      SUM ([Margin Plan (% of Sales)]) as [Margin Plan (% of Sales)]

       

      RESIDENT CE_All_TEMP

      GROUP BY [Vehicle_Name],%XRATE_KEY2, %XRATE_KEY1;

       

       

       

      CE_All_xtraFields:

      LEFT JOIN (CE_All)

      LOAD

      DISTINCT %XRATE_KEY1,

      %XRATE_KEY2,

      AOP_Data_Flag,

      [Record Currency],

      Type,

      [Period block/year] as %YearMonth,

        [Period block/year]

      RESIDENT CE_All_TEMP

      //GROUP BY %XRATE_KEY2, %XRATE_KEY1

      ;

       

       

      DROP TABLE CE_All_TEMP;

       

       

      Any Suggestion????

       

      Thanks,

      AS