topic Re: routine to split single record into multiple records in Application Development and Automation Discussions

routine to split single record into multiple records

Former Member — Thu, 04 Jan 2007 17:13:33 GMT

Hi,

Here i have a scenario where i have to extract data from the flatfile.while loading the data from flat file to the BW i need to split single record into multiple recordsBelow is the flat file structure and the required format in BW .Can any one help me out how and where to write the routine.It wud be great help if any one help me out with the code.

FLAT FILE STRUCTURE:

ITEM MONTH YEAR KF1 KF2 KF3

aaa 10 2006 10 20 30

This record shud be splitted into below format when loading the data into BW

ITEM CALMONTH KF

aaa 102006 10 (KF1 value)

aaa 092006 20 (KF2 value)

aaa 082006 30 (KF3 value)

Thanks,

Jag

Re: routine to split single record into multiple records

Former Member — Thu, 04 Jan 2007 17:34:58 GMT

Hi,

While uploading a flat file, are you using an internal table of string or an internal table with columns ?

What is the flat file format ? is it a txt file or a xls file ?

Also, is it certain that only first 3 columns will be static ?

Reward points if the answer is helpful.

Regards,

Mukul

Re: routine to split single record into multiple records

Former Member — Thu, 04 Jan 2007 17:39:27 GMT

you have to first upload the flat file into a internal table as it is and then manipulate as required by you.

loop at itab.

do 3 times.

enddo.

endloop.

regards

kishore

Re: routine to split single record into multiple records

Former Member — Thu, 04 Jan 2007 17:39:34 GMT

Hi,

Is the number of columns fixed..or it differs..KF1 KF2 KF3..OR KF1 KF2 KF3 KF4..KFn.

Thanks,

Naren

Re: routine to split single record into multiple records

Former Member — Thu, 04 Jan 2007 17:50:22 GMT

FLAT FILE STRUCTURE:

ITEM MONTH YEAR KF1 KF2 KF3

aaa 10 2006 10 20 30

check how many times u have to do.

val1 = 1.

do <no. of times>.

itab-item = <item>'aaa'.

itab-cal = <cal>'102006'.

itab-month = <mon>'10'.

itab-val = <val>'KF1'.

append itab.

clear itab.

mon = mon - 1.

val1 = val1 + 1.

concatenate 'KF' val1 into val.

enddo.

Re: routine to split single record into multiple records

Former Member — Thu, 04 Jan 2007 17:50:59 GMT

Hi Mukul,

My flat file format is Xls.csv format. I did not get you wht do you mean by 3 columns will be static. Wht iam trying to do is while loading the data i want to write a start /update routine which can split the records into my required format and load into the data target.Plz Let me know if u want me to give any more details.

Thanks,

Jag.

Re: routine to split single record into multiple records

Former Member — Thu, 04 Jan 2007 17:53:26 GMT

Hi Naren ,

My number of columns are fixed to 24. please let me know if u have any idea.

Thanks,

Jag.

Re: routine to split single record into multiple records

Former Member — Thu, 04 Jan 2007 17:55:36 GMT

Hi,

In the file structure the number of columns is fixed to 24

ITEM MONTH YEAR KF1 KF2 KF3 KF4 KF5 .... KF24

Please correct me if I am wrong..

Thanks,

Naren

Re: routine to split single record into multiple records

Former Member — Thu, 04 Jan 2007 18:03:52 GMT

Yes Naren you are right. it wud be great if u can let me know the code.

Thanks,

Jag

Re: routine to split single record into multiple records

Former Member — Thu, 04 Jan 2007 18:08:55 GMT

Hi Jag,

I meant that first 3 columns will be common for each record. Am i right ?

I think following code will sove your problem.

Upload the file in an internal table (itab1).

Declare another internal table with the required structure(itab2).

loop at itab1.

clear : it_itab2.

split itab1 at ',' into itab2-item itab2-month itab2-year l_string.

do 20 times.

split l_string at ',' into itab2-condition l_string.

append it_itab2.

enddo.

itab2-condition = l_string.

append it_itab2.

clear : itab1, itab2.

endloop.

Reward points if the answer is helpful.

Regards,

Mukul

Re: routine to split single record into multiple records

Former Member — Thu, 04 Jan 2007 18:21:19 GMT

Hi,

Check this example...I simulated your scenario..

DATA: BEGIN OF ITAB_FILE OCCURS 0,

ITEM(3),

MONTH(2),

YEAR(4),

KF1(2),

KF2(2),

KF3(2),

KF4(2),

KF5(2),

END OF ITAB_FILE.

DATA: BEGIN OF ITAB_OUTPUT OCCURS 0,

ITEM(3),

MONTH_YEAR(6),

KF(2),

END OF ITAB_OUTPUT.

DATA: V_INDEX TYPE SYINDEX.

FIELD-SYMBOLS: <FS>.

INPUT FILE DATA.

<b>ITAB_FILE-ITEM = 'aaa'.

ITAB_FILE-MONTH = '10'.

ITAB_FILE-YEAR = '2006'.

ITAB_FILE-KF1 = '10'.

ITAB_FILE-KF2 = '20'.

ITAB_FILE-KF3 = '30'.

ITAB_FILE-KF4 = '40'.

ITAB_FILE-KF5 = '50'.

APPEND ITAB_FILE.</b>

LOOP AT ITAB_FILE.

V_INDEX = 4.

MOVE THE ITEM

ITAB_OUTPUT-ITEM = ITAB_FILE-ITEM.

MOVE THE MONTH AND YEAR.

CONCATENATE ITAB_FILE-MONTH ITAB_FILE-YEAR

INTO ITAB_OUTPUT-MONTH_YEAR.

DO.

ASSIGN COMPONENT V_INDEX OF STRUCTURE ITAB_FILE TO <FS>.

IF SY-SUBRC <> 0.

EXIT.

ENDIF.

MOVE THE KF values.

ITAB_OUTPUT-KF = <FS>.

APPEND ITAB_OUTPUT.

V_INDEX = V_INDEX + 1.

ENDDO.

ENDLOOP.

LOOP AT ITAB_OUTPUT.

WRITE: / ITAB_OUTPUT-ITEM,ITAB_OUTPUT-MONTH_YEAR,ITAB_OUTPUT-KF.

ENDLOOP.

Thanks,

Naren