How to use collect statement properly

[Former Member]

In PBID table , i will get 4 rows for a material . I will get the corresponding values of bdzei from the same table . Now i will pass this bdzei into pbhi table. then i will get some 200 rows of data. I have to sum up the field plnmg , by grouping the laeda field of pbhi. In short, i need to know the sum of pbhi-plnmg for a particular pbhi-laeda . I know a way to do it. But i want to know how to use the COLLECT statement for this purpose. My output should contain oly 1 line for 1 material ..

PBID table

-

---

Matnr BDZEI

p4471 457

1002

2309

2493

PBHI table

-

---

BDZEI LAEDA PLNMG

-

---

1002 06.08.2004 0.000

1002 06.08.2004 83.000

457 07.08.2004 12.000

457 07.08.2004 24.000

Reqd O/p

MATNR LAEDA PLNMG

-

---

p4471 06.08.2004 83

p4471 07.08.2004 36

Hope u understood my situation .please help me out ...

Thanking you in advance ..

Shankar

[Former Member]

Sorry PBID Contents look like this ..

PBID table

-

---

Matnr BDZEI

p4471 457

p4471 1002

p4471 2309

p4471 2493

[Former Member]

Sorry for my disturbance, Its working fine.. simple collect statement is working fine.. sorry for the disturbance..

[former_member534411]

Shankar:

Create another internal table as same like pbdid bdzei and do as shown.

loop at pbdi.

move-corresponding pbdi to new internal table.

<b>collect new internal table.</b>

endloop.

[former_member534411]

Shankar:

<b>sorry small mistake.</b>Create another internal table as same like pbid and do as shown.

loop at pbid.

move-corresponding pbid to new internal table.

<b>collect new internal table.</b>

endloop.

[Former Member]

ya.. exactly.. that is what i had done. Its already working fine. I am sorry for disturbing all of you ..

Thank you for looking into this .

[Former Member]

Now I am facing a problem..

Itab

-

---

material SomeQty SomeDate

-

---

p0001 10 10.09.2004

p0001 15 10.09.2004

p0001 20 11.09.2004

p0002 05 10.09.2004

p0002 10 10.09.2004

Reqd o/p is this

material Qty Date

-

---

p0001 25 10.09.2004

p0001 20 11.09.2004

p0002 15 10.09.2004

How can i use the collect statement to collect the data based on the material and the date ..

The internal table is sorted based on matnr and date

please help me .

[Former Member]

Hello Shankar,

There are a few things that you need to keep in mind when you use the COLLECT statement -

1. Type of your internal table.

2. Order of the fields in your internal table.

3. The TABLE KEY of the internal table.

Assuming that you are using a standard internal table with the default key and the fields specified in the order you have mentioned, I don't see any problem with getting the output that you require (summing up for each different combination of material and date).

I shall give you some code which you can cross check with yours for further troubleshooting -

DATA: BEGIN OF LINE,
        MATNR TYPE MATNR,
        PLNMG TYPE PLNMG,
        LAEDA TYPE LAEDA,
      END OF LINE.

DATA ITAB LIKE STANDARD TABLE
            OF LINE
          WITH DEFAULT KEY. 
          
DATA ITAB2 LIKE ITAB.


LINE-MATNR = 'A'.
LINE-LAEDA = SY-DATUM.
LINE-PLNMG = 10.
APPEND LINE TO ITAB.
*COLLECT LINE INTO ITAB.

LINE-MATNR = 'A'.
LINE-LAEDA = SY-DATUM.
LINE-PLNMG = 15.
APPEND LINE TO ITAB.
*COLLECT LINE INTO ITAB.


LINE-MATNR = 'A'.
LINE-LAEDA = '20041010'.
LINE-PLNMG = 6.
APPEND LINE TO ITAB.
*COLLECT LINE INTO ITAB.

LOOP AT ITAB INTO LINE.
  COLLECT LINE INTO ITAB2.
  WRITE  / LINE-PLNMG.
ENDLOOP.

SKIP 6.

LOOP AT ITAB2 INTO LINE.
  WRITE  : / LINE-PLNMG.
ENDLOOP.

Regards,

Anand Mandalika.

P.S: You have not said if the problem has been resolved or not. If it is, then please do mark the question as answered.

[Former Member]

Dear poornanand,

ur code is working as per my requirement.. But i dont understand where u r specifying that it should collect based on the combination of material and date . I tried to put tthe same code in my program But its not workingin that . Instead of giving values as hard-coded, the only difference in my program is that i have a loop and i pass the values to line in that loop and append it. But anyhow its not working in my report .. Please help me .. Tell me where u r specifying that it should collect based on the combination of material and date .

Hoping a fast reply ..

Thanking you ..

[Former Member]

Hello Shankar,

In the example that I have given above, there was no need to specify the combination as MATERIAL, DATE. Since I have defined the internal table with athe default key, it means that all the non-numeric fields will comprise the key.

If I'm not wrong, then your internal table must be having more than the three fields in my example.

You have said that you were <i>appending</i> the values to the internal table in the loop. One thing that you must probably try is to COLLECT the values into the internal table rather than append them. However, as long as you are using another internal table to collect the data, there should not be a problem even if you append it, as I have done in my example.

If your problem is still not resolved, then please do give the code(along with the data declarations), that you are using so that we can find the bug faster.

Hoping to see your problem resolved soon,

Regards,

Anand Mandalika.

[Former Member]

REPORT zppr_zpipr NO STANDARD PAGE HEADING LINE-SIZE 150 LINE-COUNT 63.

TABLES: pbid,

pbhi,

makt,

mseg,

mkpf.

DATA: BEGIN OF it_pbid OCCURS 0,

matnr LIKE pbid-matnr, " Material

status TYPE c LENGTH 4, " For distinguishing materials from pbid and pbim .. will contain space for PBID and 'PBIM' for PBIM

bdzei LIKE pbid-bdzei,

laeda LIKE pbhi-laeda,

end of it_pbid.

DATA: BEGIN OF it_pbim OCCURS 0,

matnr LIKE pbid-matnr, " Material

status TYPE c LENGTH 4, " For distinguishing materials from pbid and pbim .. will contain space for PBID and 'PBIM' for PBIM

bdzei LIKE pbim-bdzei,

laeda LIKE pbhi-laeda,

end of it_pbim.

DATA: BEGIN OF it_pbid_pbim OCCURS 0,

matnr LIKE pbid-matnr, " Material

laeda LIKE pbhi-laeda, " Reduction Date

dbmng LIKE pbhi-dbmng, " Planned quantity in the data base

plnmg LIKE pbhi-plnmg, " Planned quantity

status TYPE c LENGTH 4, " For distinguishing materials from pbid and pbim .. will contain space for PBID and 'PBIM' for PBIM

mblnr LIKE mseg-mblnr, " Material Doc Number

pbfnr LIKE pbid-pbdnr, " Plan Number

maktx LIKE makt-maktx, " Matl Desc

aenam LIKE pbhi-aenam, " User Changed

erfmg LIKE mseg-erfmg, " Qty Invoiced

budat LIKE mkpf-budat, " Invoice date

bdzei LIKE pbid-bdzei, " Independent requirements pointer

werks LIKE pbid-werks, " plant

pirrednqty TYPE i, " PIR Reduction Quantity = pbih-plnmg - pbih-dbmng

diff TYPE i, " Difference

slno TYPE i, " Sl No

END OF it_pbid_pbim.

DATA: BEGIN OF it_allrows OCCURS 0.

INCLUDE STRUCTURE it_pbid_pbim.

DATA: END OF it_allrows.

*DATA: BEGIN OF it_final OCCURS 0.

• INCLUDE STRUCTURE it_pbid_pbim.

*DATA: END OF it_final.

DATA: BEGIN OF line,

matnr LIKE pbid-matnr, " Material

laeda LIKE pbhi-laeda, " Reduction Date

plnmg LIKE pbhi-plnmg, " Planned quantity

maktx LIKE makt-maktx, " Matl Desc

dbmng LIKE pbhi-dbmng, " Planned quantity in the data base

mblnr LIKE mseg-mblnr, " Material Doc Number

pbfnr LIKE pbid-pbdnr, " Plan Number

aenam LIKE pbhi-aenam, " User Changed

erfmg LIKE mseg-erfmg, " Qty Invoiced

budat LIKE mkpf-budat, " Invoice date

bdzei LIKE pbid-bdzei, " Independent requirements pointer

werks LIKE pbid-werks, " plant

pirrednqty TYPE i, " PIR Reduction Quantity = pbih-plnmg - pbih-dbmng

diff TYPE i, " Difference

slno TYPE i, " Sl No

status TYPE c LENGTH 4, " For distinguishing materials from pbid and pbim .. will contain space for PBID and 'PBIM' for PBIM

END OF line.

DATA Itfinal1 LIKE STANDARD TABLE

OF LINE

WITH DEFAULT KEY.

DATA ITfinal LIKE ITfinal1.

DATA: BEGIN OF it_dates OCCURS 0,

date TYPE sy-datum,

END OF it_dates.

DATA: l_slno TYPE i.

DATA: l_zebra TYPE c.

SELECT-OPTIONS:

s_werks FOR pbid-werks obligatory.

SELECT-OPTIONS:

s_matnr FOR pbid-matnr.

SELECT-OPTIONS:

s_pbdnr FOR pbid-pbdnr.

SELECT-OPTIONS:

s_laeda FOR pbhi-laeda obligatory.

parameter:

c_print type checkbox.

SELECT matnr bdzei FROM pbid INTO (it_pbid-matnr, it_pbid-bdzei) WHERE werks IN s_werks AND matnr IN s_matnr AND pbdnr IN s_pbdnr.

it_pbid-status = 'PBID'.

APPEND it_pbid.

• move-corresponding it_pbid to it_pbid_pbim.

• Append it_pbid_pbim.

ENDSELECT.

SELECT matnr bdzei FROM pbim INTO (it_pbim-matnr,it_pbim-bdzei) WHERE werks IN s_werks AND matnr IN s_matnr AND pbdnr IN s_pbdnr.

APPEND it_pbim.

• Append it_pbid_pbim.

ENDSELECT.

*break-point.

START-OF-SELECTION.

LOOP AT s_laeda.

it_dates-date = s_laeda-low.

APPEND it_dates.

ENDLOOP.

DATA: l_startdate LIKE sy-datum.

IF s_laeda-high EQ space.

ELSE.

l_startdate = s_laeda-low + 1.

DO.

IF l_startdate <= s_laeda-high.

it_dates-date = l_startdate.

APPEND it_dates.

ELSE.

it_dates-date = sy-datum.

EXIT.

ENDIF.

l_startdate = l_startdate + 1.

ENDDO.

ENDIF.

*break-point.

LOOP AT it_pbim.

LOOP AT it_dates.

it_pbim-laeda = it_dates-date.

it_pbim-status = 'PBIM'.

MODIFY it_pbim TRANSPORTING laeda status.

MOVE-CORRESPONDING it_pbim TO it_pbid_pbim.

APPEND it_pbid_pbim.

ENDLOOP.

ENDLOOP.

break-point.

l_zebra = 'X'.

DATA: l_toterfmg LIKE mseg-erfmg.

DATA: l_erfmg LIKE mseg-erfmg.

data: l_totpir type i.

**************************************PBID*************************************

LOOP AT it_pbid.

move-corresponding it_pbid to it_pbid_pbim.

append it_pbid_pbim.

SELECT SINGLE maktx FROM makt INTO (it_pbid_pbim-maktx) WHERE matnr = it_pbid-matnr.

MODIFY table it_pbid_pbim TRANSPORTING maktx.

SELECT aenam laeda FROM pbhi INTO (it_pbid_pbim-aenam,it_pbid_pbim-laeda) WHERE bdzei = it_pbid-bdzei AND aenam = 'Abbau-'.

MODIFY TABLE it_pbid_pbim TRANSPORTING aenam laeda. " debug here

select single sum( dbmng ) sum( plnmg ) FROM pbhi INTO (it_pbid_pbim-dbmng,it_pbid_pbim-plnmg) WHERE bdzei = it_pbid-bdzei AND aenam = 'Abbau-' and laeda = it_pbid_pbim-laeda..

MODIFY TABLE it_pbid_pbim TRANSPORTING dbmng plnmg. " debug here

• endselect.

it_pbid_pbim-pirrednqty = it_pbid_pbim-dbmng - it_pbid_pbim-plnmg.

MODIFY table it_pbid_pbim TRANSPORTING pirrednqty.

IF ( it_pbid_pbim-laeda IN s_laeda AND it_pbid_pbim-aenam EQ 'Abbau-' ).

MOVE-CORRESPONDING it_pbid_pbim TO it_allrows.

append it_allrows.

ELSEIF NOT it_pbid_pbim-laeda IN s_laeda.

• delete it_pbid_pbim index sy-tabix. " debug here

ENDIF.

ENDSELECT.

ENDLOOP.

**************************************PBIM*************************************

LOOP AT it_pbim.

move-corresponding it_pbim to it_pbid_pbim.

append it_pbid_pbim.

SELECT SINGLE maktx FROM makt INTO (it_pbid_pbim-maktx) WHERE matnr = it_pbim-matnr.

MODIFY table it_pbid_pbim TRANSPORTING maktx.

SELECT aenam laeda FROM pbhi INTO (it_pbid_pbim-aenam,it_pbid_pbim-laeda) WHERE bdzei = it_pbim-bdzei AND aenam = 'Abbau-'.

MODIFY TABLE it_pbid_pbim TRANSPORTING aenam laeda. " debug here

select single sum( dbmng ) sum( plnmg ) FROM pbhi INTO (it_pbid_pbim-dbmng,it_pbid_pbim-plnmg) WHERE bdzei = it_pbim-bdzei AND aenam = 'Abbau-' and laeda = it_pbid_pbim-laeda..

MODIFY TABLE it_pbid_pbim TRANSPORTING dbmng plnmg. " debug here

it_pbid_pbim-pirrednqty = it_pbid_pbim-dbmng - it_pbid_pbim-plnmg.

MODIFY table it_pbid_pbim TRANSPORTING pirrednqty.

IF ( it_pbid_pbim-laeda IN s_laeda AND it_pbid_pbim-aenam EQ 'Abbau-' ).

MOVE-CORRESPONDING it_pbid_pbim TO it_allrows.

append it_allrows.

ELSEIF NOT it_pbid_pbim-laeda IN s_laeda.

• delete it_pbid_pbim index sy-tabix. " debug here

ENDIF.

ENDSELECT.

ENDLOOP.

sort it_allrows by matnr laeda status.

**********************************ALL ROWS************************

loop at it_allrows.

line-matnr = it_allrows-matnr.

line-laeda = it_allrows-laeda.

line-plnmg = it_allrows-plnmg.

line-dbmng = it_allrows-dbmng.

• line-mblnr = it_allrows-mblnr.

• line-pbfnr = it_allrows-pbfnr.

line-maktx = it_allrows-maktx.

• line-aenam = it_allrows-aenam.

• line-erfmg = it_allrows-erfmg.

• line-budat = it_allrows-budat.

line-bdzei = it_allrows-bdzei.

• line-werks = it_allrows-werks.

line-pirrednqty = it_allrows-pirrednqty.

line-diff = it_allrows-diff.

line-slno = it_allrows-slno.

line-status = it_allrows-status.

collect line into itfinal1.

endloop.

loop at itfinal1 into line.

collect line into itfinal.

write: / line-matnr, line-plnmg, line-dbmng,line-laeda,line-status..

endloop.

skip 4.

loop at itfinal into line.

write: / line-matnr, line-plnmg, line-dbmng,line-laeda,line-status..

endloop.

break-point.

[Former Member]

I got the bug. Its bcoz its collecting based on combination of matnr, laeda nad bdzei.. which is not needed. So, I am not appending bdzei to line.. Now its working..

Thank You ..

[Former Member]

Hello Shankar,

As expected, in your internal table, you have more than the three fields I have used in my example. That is essentially the reason why the COLLECT statement does not work as you desire.

The collect statement always treats all the non-numeric fields as the key fields. You cannot specify the Key Fields explicitly. That is to say, the internal table should always be declared with the default key.

In your case, the only fields that you seem to require in the internal table IT_FINAL are <b>matnr, plnmg, dbmng, laeda and status</b>. So ideally, you must be declaring the internal table with only these fields. However, even if you did that, in this case, there is another field called STATUS, which is a character field(non-numeric). So that will also be taken as the key field by the COLLECT statement.

That leaves us with only one conclusion : the <i>collect</i> statement is not suitable for your requirement. You must still declare the internal table IT_FINAL with only those fields that you require (for example, to display on the list). Then you can use the control level processing for displaying the contents based on your requirement.

Regards,

Anand Mandalika.

[Former Member]

Hi Shankar,

1. What i understand from the last problem is -

The combination of MATNR and DATE is different

and hence the totalling is done for each combination.

2. Your requirement is that for one MATNR there

should be only one total.

3. For This purpose,

the NEW ITAB which you create

SHOULD NOT CONTAIN the DATE column.

4. It should contain only MATNR and TOTAL.

5. After this the collect statement will take care.

I m new to this sdn. If you find

the answer useful, pls give points.

Sample program is :

*----

---

REPORT abc.

DATA : BEGIN OF itab OCCURS 0,

f1(10) TYPE c,

dt TYPE sy-datum,

num TYPE i,

END OF itab.

*----

---

Do Not Use The same structure

*data : tot like table of itab with header line.

*----

---

Instead Use This

DATA : BEGIN OF tot OCCURS 0,

f1(10) TYPE c,

num TYPE i,

END OF tot.

*------- Sample Data

itab-f1 = 'A'. itab-num = 3.

itab-dt = '20040401'.

APPEND itab.

itab-f1 = 'A'. itab-num = 3.

itab-dt = '20040401'.

APPEND itab.

itab-f1 = 'B'. itab-num = 5.

itab-dt = '20040401'.

APPEND itab.

itab-f1 = 'A'. itab-num = 3.

itab-dt = '20040402'.

APPEND itab.

*------- Loop and collect

LOOP AT itab.

MOVE-CORRESPONDING itab TO tot.

COLLECT tot.

ENDLOOP.

[Former Member]

Dear Shankar,

When using collect,

(if table key is not specified,)

then

all NON-NUMERIC fields UNIQUE combination

will be considered as key for that table.

Regards,

Amit Mittal.

[Former Member]

Dear Shankara,

1 . The sql whereever name of user is required,

should be in CAPITAL.

SELECT aenam laeda FROM pbhi INTO

(it_pbid_pbim-aenam,it_pbid_pbim-laeda)

WHERE bdzei = it_pbim-bdzei AND aenam = <b>'Abbau-</b>'.

Because of this, records are not coming in

the required internal table.

Regards,

Amit Mittal.

[Former Member]

Hi,

I am having all the fields in one internal table .

I just want to compare the below mentioned fields

material group,

material,

glaccounts,

documentnumber,

fiscal year,

plants

and to add the amountin local currency.(wrbtr)

I am using the collect.please tell , the collect statement will work.

can any one please help me .

codings:

SELECT SAKNR FROM SKA1 INTO CORRESPONDING FIELDS OF TABLE IT_SKA1

WHERE KTOPL IN S_KTOPL.

IF NOT IT_SKA1[] IS INITIAL.

SELECT

SAKNR

INTO CORRESPONDING FIELDS OF TABLE IT_SKB1

FROM SKB1

FOR ALL ENTRIES IN IT_SKA1

WHERE SAKNR EQ IT_SKA1-SAKNR

AND BUKRS IN S_BUKRS.

ENDIF.

IF NOT IT_SKB1[] IS INITIAL.

SELECT

HKONT BELNR

INTO CORRESPONDING FIELDS OF TABLE IT_BSIS

FROM BSIS FOR ALL ENTRIES IN IT_SKB1

WHERE HKONT EQ IT_SKB1-SAKNR AND GJAHR IN S_GJAHR.

ENDIF.

IF NOT IT_BSIS[] IS INITIAL.

SELECT

HKONT

BELNR

GJAHR

WRBTR

MATNR

WERKS

INTO CORRESPONDING FIELDS OF TABLE IT_BSEG

FROM BSEG FOR ALL ENTRIES IN IT_BSIS

WHERE BELNR EQ IT_BSIS-BELNR

AND HKONT EQ IT_BSIS-HKONT

AND MATNR IN S_MATNR

AND GJAHR IN S_GJAHR

AND WERKS IN S_WERKS.

ENDIF.

IF SY-SUBRC EQ 0.

SELECT

A~MATNR

A~WERKS

B~MATKL

INTO

CORRESPONDING FIELDS OF TABLE IT_COMBINE FROM MARC AS A INNER JOIN

MARA AS B ON B_{MATNR = A}MATNR FOR ALL ENTRIES IN IT_BSEG

WHERE A~MATNR = IT_BSEG-MATNR

AND A~WERKS = IT_BSEG-WERKS.

ENDIF.

SORT IT_BSEG BY MATNR.

SORT IT_COMBINE BY MATNR.

CLEAR: IT_COMBINE.

CLEAR: IT_BSEG.

LOOP AT IT_COMBINE.

READ TABLE IT_BSEG WITH KEY MATNR = IT_COMBINE-MATNR.

IF SY-SUBRC EQ 0.

IT_BSEG-WERKS = IT_COMBINE-WERKS.

IT_BSEG-MATKL = IT_COMBINE-MATKL.

MODIFY TABLE IT_BSEG.

ENDIF.

IF SY-SUBRC NE 0.

IT_BSEG-WERKS = IT_COMBINE-WERKS .

IT_BSEG-MATKL = IT_COMBINE-MATKL .

IT_BSEG-MATNR = IT_COMBINE-MATNR .

APPEND IT_BSEG.

ENDIF.

ENDLOOP.

SORT it_bseg by matkl hkont.

loop at it_bseg.

*wa_final-MATKL = IT_BSEG-MATKL.

wa_final-MAtnr = IT_BSEG-MAtnr.

wa_final-werks = it_bseg-werks.

wa_final-HKONT = IT_BSEG-HKONT.

wa_final-belnr = IT_BSEG-belnr.

wa_final-gjahr = it_bseg-gjahr.

wa_final-wrbtr = it_bseg-wrbtr.

COLLECT wa_final INTO it_bseg1.

clear wa_final.

endloop.

loop at it_bseg1.

write:/10 it_bseg1-matnr,20 it_bseg1-hkont,35 it_bseg1-belnr,45

it_bseg1-gjahr,60 it_bseg1-wrbtr,85 it_bseg1-matkl,100 it_bseg1-werks.

Thanks in Advance.