cancel
Showing results for 
Search instead for 
Did you mean: 

How to transpose Master data records from Row to Column

Former Member
0 Kudos

Hi Gurus,

I came across one requirement where I need to transpose Master Data records from Rows to Column.

Is there any alternative to Expert Routine? Development will be in BI7.3

We are getting some Material information from function Module datasource in below format.

Key part here is "Material & Char Type"

MaterialCharacteristic typeChar Value
1234Z00_PART_ColorBlue
1234Z01_Part_TypeRaw
1234Z01_Part_plantUSCH
1234Z01_Part_vendorFord
1234

We need above information to be loaded as part of Material Attr.

Key here will be only "Material".

MaterialcolorTypePlantVendor
1234BlueRawUSCHFord

I'm trying to avoid Expert Routine, but if thats the only option, what could be the best logic for it considering huge amount of data.

Thanks,

Deepak Jain

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi All,

We are able to nail this scenario. Sorry for late reply. Below is the approach we have implemented.

  1. Get all the data as it is from source to standard DSO, lets say “ZSOURCE_DSO” in Staging layer with Key as “Material & CharType”. This is optional, but for us it was as per design protocol. Transformation from ECC Datasource ZSOURCE_ATTR to ZSOURCE_DSO for all fields will be 1:1.
  2. The DSO will be having data as it is in Source & at multiple Rows which needed to be transposed.

Material

Characteristic type

ZCHAR_VAL ale

1234

Z00_PART_Color

Blue

1234

Z01_Part_Type

Raw

1234

Z01_Part_plant

USCH

1234

Z01_Part_vendor

Ford

   3.  In Info-object “Material”, add all required Attributes. These will be filled on same row with Key as Material and from the DSO.

Material

Color

Type

Plant

Vendor

    4. Create transformation between DSO & InfoObject.

    5. Map Material as 1:1, as it’s the only Key in Info-object.

    6. In End Routine, write the below code for Transposing the Rows in to Columns.


DATA: it_ZSOURCE_DSO TYPE TABLE OF /BIC/AZSOURCE_DSO00, 

      wa_ZSOURCE_DSO LIKE LINE OF it_ZSOURCE_DSO.

*** Variables

DATA: v_color  TYPE /BIC/OIZCOLOR,     “ Variables for different columns

      v_type   TYPE /BIC/OIZTYPE,

      v_plant  TYPE /BIC/OIZPLANT,

      v_vendor TYPE /BIC/OIZVENDOR.

*** ZSOURCE_DSO is getting data from datasource ZBW_DATASOURCE.

CLEAR it_ZSOURCE_DSO[].

SELECT * FROM /BIC/AZSOURCE_DSO00 INTO TABLE it_ZSOURCE_DSO

   FOR ALL ENTRIES IN RESULT_PACKAGE

     WHERE /bic/ZMATERIAL = RESULT_PACKAGE-/BIC/ZMATERIAL

       AND /bic/ZCHARTYP  = RESULT_PACKAGE-/BIC/ZCHARTYP.

SORT it_ZSOURCE_DSO ASCENDING BY /BIC/ZMATERIAL /BIC/ZCHARTYP.


*** Loop at RESULT_PACKAGE.

LOOP AT RESULT_PACKAGE ASSIGNING <RESULT_FIELDS>.

     CLEAR wa_ZSOURCE_DSO.

     CLEAR v_color.

     CLEAR v_type.

     CLEAR v_plant.

     CLEAR v_vendor.

    

*** Loop at Work Area.

     LOOP AT it_ZSOURCE_DSO INTO  wa_ZSOURCE_DSO WHERE

             /BIC/ZMATERIAL = <RESULT_FIELDS>-/BIC/ZMATERIAL.

*** Get respective Char value based on Char Case

       CASE wa_ZSOURCE_DSO-/bic/ZCHARTYP.

         WHEN 'Z00_PART_COLOR’.

           v_color   = wa_ZSOURCE_DSO-/bic/ZCHAR_VAL.

         WHEN ‘Z01_PART_TYPE.

           v_type    = wa_ZSOURCE_DSO-/bic/ ZCHAR_VAL+28.

         WHEN ' Z01_PART_PLANT'.

           v_plant   = wa_ZSOURCE_DSO-/bic/ZCHAR_VAL.

         WHEN ' Z01_PART_VENDOR'.

           v_vendor  = wa_ZSOURCE_DSO-/bic/ZCHAR_VAL.

         WHEN OTHERS.

       ENDCASE.

     ENDLOOP.

*** Updating Result_fields

  <RESULT_FIELDS>-/BIC/ZZCOLOR = v_color.

  <RESULT_FIELDS>-/BIC/ZTYPE   = v_type.

  <RESULT_FIELDS>-/BIC/ZPLANT  = v_plant.

  <RESULT_FIELDS>-/BIC/ZVENDOR = v_vendor.

       ENDLOOP.

   7. Lastly, Make DTP setting with:

  • Semantic Key : Material
  • Package Size : 15000

   8. Finally, in Material, we have all the data in one row.

Material

Color

Type

Plant

Vendor

1234

Blue

RAW

USCH

FORD

This worked very efficiently even for huge amount of data.

Regards,

Deepak Jain

Answers (4)

Answers (4)

former_member182998
Active Participant
0 Kudos

Hello Deepak ,

I agree you have achieved the transpose with the End Routine as per the Step 6.

Would like to know what is significance of Step 7 in  that scenario ?

           7.Lastly, Make DTP setting with:

    • Semantic Key : Material
    • Package Size : 15000

Regards

YN

Former Member
0 Kudos

Yogesh,

Its for Performance Tuning.

As its Master Data, system will suggest to have Sementic Key while DTP Activation.

And its always better to have smaller Package size if you have Routines in transformation.

   Just to elaborate, With Package size of 50000 while loading a Million record, DTP will run for an hour, while its running for just 12 mins with package size of 15000.

Regards,

Deepak

Former Member
0 Kudos

HI Deepak,

You can use an APD.. ( Source is Master data and Target is master data).

You have to the choose the transformation : Transform list into  data Record.

There you have transpose field.

Regards,

Rajesh

Former Member
0 Kudos

Hi Deepak,


I think you may have to go with expert routine.

If you decide to do that - you can refer below code. This code works fine in transpose.

In expert routine, ofcourse the table names and etc with change which can be done easily.

this is just a sample code my own naming conventions and sample data you provided.

-------------------------------------------------------------------------------------------------------------------------------

   TYPES: BEGIN OF ty_mat,
        mat TYPE matnr,
        char TYPE c LENGTH 50,
        chval TYPE c LENGTH 10,
       END OF ty_mat,
       BEGIN OF ty_chval,
        mat TYPE matnr,
        col TYPE c LENGTH 10,
        typ TYPE c LENGTH 10,
        plant TYPE werks_d,
        vend TYPE kunnr,
       END OF ty_chval.

DATA: lt_mat TYPE STANDARD TABLE OF ty_mat,
      ls_mat LIKE LINE OF lt_mat,
      lt_chval TYPE STANDARD TABLE OF ty_chval,
      ls_chval LIKE LINE OF lt_chval.
FIELD-SYMBOLS: <fs_mat> TYPE ty_mat,
               <fs_chval> TYPE ty_chval,
               <fs_mat1> TYPE ty_mat.

* fill up the data
ls_mat-mat = '1234'. ls_mat-char = 'Z00_PART_COLOR'.
ls_mat-chval = 'Blue'. APPEND ls_mat TO lt_mat.

ls_mat-mat = '1234'. ls_mat-char = 'Z00_PART_TYPE'.
ls_mat-chval = 'Raw1'. APPEND ls_mat TO lt_mat.

ls_mat-mat = '1234'. ls_mat-char = 'Z00_PART_PLANT'.
ls_mat-chval = 'USCH'. APPEND ls_mat TO lt_mat.

ls_mat-mat = '1234'. ls_mat-char = 'Z00_PART_VENDOR'.
ls_mat-chval = 'Ford'. APPEND ls_mat TO lt_mat.

ls_mat-mat = '5678'. ls_mat-char = 'Z00_PART_COLOR'.
ls_mat-chval = 'RED'. APPEND ls_mat TO lt_mat.

ls_mat-mat = '5678'. ls_mat-char = 'Z00_PART_TYPE'.
ls_mat-chval = 'Raw2'. APPEND ls_mat TO lt_mat.

ls_mat-mat = '5678'. ls_mat-char = 'Z00_PART_PLANT'.
ls_mat-chval = 'MMMM'. APPEND ls_mat TO lt_mat.

ls_mat-mat = '5678'. ls_mat-char = 'Z00_PART_VENDOR'.
ls_mat-chval = 'CHEVY'. APPEND ls_mat TO lt_mat.

SORT lt_mat BY mat.
READ TABLE lt_mat ASSIGNING <fs_mat1> INDEX 1.
LOOP AT lt_mat ASSIGNING <fs_mat>.
  IF <fs_mat>-mat EQ <fs_mat1>-mat.
    MOVE: <fs_mat>-mat TO ls_chval-mat.
    CASE <fs_mat>-char.
      WHEN 'Z00_PART_COLOR'.
        ls_chval-col = <fs_mat>-chval.
      WHEN 'Z00_PART_TYPE'.
        ls_chval-typ = <fs_mat>-chval.
      WHEN 'Z00_PART_PLANT'.
        ls_chval-plant = <fs_mat>-chval.
      WHEN 'Z00_PART_VENDOR'.
        ls_chval-vend = <fs_mat>-chval.
      WHEN OTHERS.
    ENDCASE.
  ELSE.
    APPEND ls_chval TO lt_chval.
    CASE <fs_mat>-char.
      WHEN 'Z00_PART_COLOR'.
        ls_chval-col = <fs_mat>-chval.
      WHEN 'Z00_PART_TYPE'.
        ls_chval-typ = <fs_mat>-chval.
      WHEN 'Z00_PART_PLANT'.
        ls_chval-plant = <fs_mat>-chval.
      WHEN 'Z00_PART_VENDOR'.
        ls_chval-vend = <fs_mat>-chval.
      WHEN OTHERS.
    ENDCASE.
    <fs_mat1> = <fs_mat>.
  ENDIF.
ENDLOOP.
APPEND ls_chval TO lt_chval.

-------------------------------------------------------------------------------------------------------------------------------

Please let me know if you face any issues with this.

Thanks

Amit

RamanKorrapati
Active Contributor
0 Kudos

Hi,

Not sure, But think about rule groups, you need to create 4 rule groups and need to map different targets for each rule group.

About rule groups you may get more information  from Google. please do the simple search on it.

Thanks

Former Member
0 Kudos

Hi Ram,

Rule groups will not work here, as its Master data.

So, each time Record will get "Over-Write" with next record.

Thanks,

Deepak