Skip to Content

Quick Transposing of data from multiple rows to single row fields for a given Key field.

I recently encountered a requirement to transpose data from multiple rows to a single row for a given Key field.
Would like to clarify that there might be multiple ways to fashion this,however below is my take on it.

Scenario : Data in the source

Source (ZFAMILY)
Key Members
X Family 4
Y Family 3
Source (ZFAMILY_DET)
Key Siblings
X Family James
X Family Harry
X Family Mary
X Family Louise
Y Family Carol
Y Family Theresa
Y Family Anita

Target : To achieve the below.

Target Field Structure (SAP BW)  
Key        Sibling1 Sibling2 Sibling3 Sibling4
X Family James Harry Mary Louise
Y Family Carol Theresa Anita  

 

Sample Code to achieve this :

 

* ZFAMILY : Source Table
* ZFAMILY_DET : Source table with details on siblings.

DATA:

LT_DATA TYPE STANDARD TABLE OF ZFAMILY,
LIT_ZFAMILY_DET TYPE STANDARD TABLE OF ZFAMILY_DET,
WA_ZFAMILY_DET TYPE ZFAMILY_DET.
SELECT KEY FROM ZFAMILY INTO LT_DATA.
SELECT KEY FROM ZFAMILY_DET INTO LIT_FAMILY.

Logic :

LOOP AT LT_DATA  ASSIGNING <FS_DATA>.
LOOP AT LIT_ZFAMILY_DET INTO WA_ZFAMILY_DET WHERE KEY = <FS_DATA>-KEY
IF WA_ZFAMILY_DET-KEY IS NOT INITIAL.
IF <FS_DATA>-SIBLING1 = ”.
<FS_DATA>-SIBLING1= WA_ZFAMILY_DET-SIBLINGS.
ELSEIF <FS_DATA>-SIBLING2= ”.
<FS_DATA>-SIBLING2 = WA_ZFAMILY_DET-SIBLINGS.
ELSEIF <FS_DATA>-SIBLING3 = ”.
<FS_DATA>-SIBLING3= WA_ZFAMILY_DET-SIBLINGS.
ELSEIF <FS_DATA>-SIBLING4 = ”.
<FS_DATA>-SIBLING4 = WA_ZFAMILY_DET-SIBLINGS.
ENDLOOP.
ENDLOOP.

Cheers!
Russel

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply