Merge the rows in Internal Table

[Former Member]

Hello All,

In my Internal table,data is coming in following way

                      YB01       YB02     YB03     YB04

User1               X

User1                               X                         X

User2                               X           X

User2               X

User3               X

My requirement is

                      YB01       YB02     YB03     YB04

User1               X              X                         X

User2               X               X           X

User3               X

I Tried with Collect Query but it didn't work, Because 'X' is char field

Collect Query works with (type I,F and P)

[Former Member]

There are many ways to achieve this. Once them would be:

Lets say the first field on you table is named USER.

temp[] = it[].
sort: it by user,
      temp by user.
delete adjacent duplicates in it[] comparing user.
loop at it into <fs>.
read table temp with key user = <fs>-user
yb01 = 'X'.
if sy-subrc is initial.
<fs>-yb01 = 'X'.
endif.

perform the same reads for yb02/03/04
endloop.

Instead of adding 4 different reads inside the loop, you can add one subroutine where you pass the YB* values and update accordingly.

V.

[Former Member]

Hi Vikram,

Thanks for Quick reply.

I did not got what you did after "read table temp with key user = <fs>-user".

Should I Hard code it you mean for yb01 = 'X'?

[Former Member]

Yes. You check if each one of of the four fields YB01-YB04 is set to X. If yes, then you modify the only row in the table IT using the field symbol.

so you should have something like this inside the loop.

               read table temp with key user = <fs>-user 

                                                          yb01 = 'X'. 

               if sy-subrc is initial. 

                    <fs>-yb01 = 'X'. 

               endif.

               read table temp with key user = <fs>-user 

                                                          yb02 = 'X'. 

               if sy-subrc is initial. 

                    <fs>-yb02 = 'X'. 

               endif.

               read table temp with key user = <fs>-user 

                                                          yb03 = 'X'. 

               if sy-subrc is initial. 

                    <fs>-yb03 = 'X'. 

               endif.

               read table temp with key user = <fs>-user 

                                                          yb04 = 'X'. 

               if sy-subrc is initial. 

                    <fs>-yb04 = 'X'. 

               endif.

V.

[raymond_giuseppi]

No single statement (*) for a group by/max() on internal table, so either change your previous SQL statement or go for an easy [SORT and] LOOP AT itab coding.

Regards,

Raymond

(*) Except for some 7.40 new syntax on GROUP BY on itab...

[raymond_giuseppi]

A too complex (generic) sample :

REFRESH itab2.
SORT itab1 BY (fieldname).
LOOP AT itab1 ASSIGNING <wa1>.
  AT NEW (fieldname).
    APPEND INITIAL LINE TO itab2 ASSIGNING <wa2>.
    <wa2> = <wa1>.
  ENDAT.
  DO.
    ASSIGN COMPONENT sy-index OF STRUCTURE <wa1> TO <fs1>.
    IF <fs1> IS NOT ASSIGNED. EXIT. ENDIF.
    ASSIGN COMPONENT sy-index OF STRUCTURE <wa2> TO <fs2>.
    IF <fs1> IS NOT INITIAL.
      <fs2> = <fs1>.
    ENDIF.
  ENDDO.
ENDLOOP.

Regards,

Raymond

[former_member246634]

temp[] = itab[]

sort temp by user. sort itab by user.

delete adjacent duplicates from temp comparing user. "get unique usernames

LOOP AT temp ASSIGNING <temp>.

     CLEAR: <temp>-yb01, <temp>-yb02, <temp>-yb03, <temp>-yb04.

     LOOP AT itab ASSIGNING <itab> WHERE user = <temp>-user.

          IF <itab>--yb01 EQ 'X'

               <temp>-yb01 = 'X'

          ... proceed with other fields

          ENDIF.

     ENDLOOP.

ENDLOOP.

[Former Member]

Hello  Bartlomiej,

This will work as append i guess. I wont get desired output.

Regards,

Sujoy

[former_member246634]

No, it won't. When you use LOOP AT itab INTO wa then you should put MODIFY statement to see changes in itab. When you use ASSIGNING <fs> or REFERENCE INTO it kind of has MODIFY statement in it. In temp table in my code you will have what you need.

TYPES: BEGIN OF ty_data,
   user  TYPE char10,
   field1 TYPE char1,
   field2 TYPE char1,
   field3 TYPE char1,
   END OF ty_data.

   DATA:
         ls_data TYPE ty_data,
         lt_data TYPE TABLE OF ty_data,
         lt_temp TYPE TABLE OF ty_data.

   FIELD-SYMBOLS:
                  <temp> TYPE ty_data,
                  <data> TYPE ty_Data.

   ls_data-user = 'john'.
   ls_data-field1 = 'X'.
   ls_data-field2 = ''.
   ls_data-field3 = ''.
   APPEND ls_data to lt_data.
   ls_data-user = 'john'.
   ls_data-field1 = ''.
   ls_data-field2 = ''.
   ls_data-field3 = 'X'.
   APPEND ls_data to lt_data.
   ls_data-user = 'bob'.
   ls_data-field1 = ''.
   ls_data-field2 = ''.
   ls_data-field3 = ''.
   APPEND ls_data to lt_data.
   ls_data-user = 'bob'.
   ls_data-field1 = 'X'.
   ls_data-field2 = ''.
   ls_data-field3 = 'X'.
   APPEND ls_data to lt_data.
   ls_data-user = 'stan'.
   ls_data-field1 = 'X'.
   ls_data-field2 = 'X'.
   ls_data-field3 = 'X'.
   APPEND ls_data to lt_data.

   lt_temp[] = lt_data[].

   SORT lt_temp by user.
   SORT lt_data by user.

   DELETE ADJACENT DUPLICATES FROM lt_temp COMPARING user.

   LOOP AT lt_temp ASSIGNING <temp>.
     CLEAR: <temp>-field1, <temp>-field2, <temp>-field3.
     LOOP AT lt_data ASSIGNING <data> WHERE user = <temp>-user.
       IF <data>-field1 = 'X'.
         <temp>-field1 = 'X'.
       ENDIF.
       IF <data>-field2 = 'X'.
         <temp>-field2 = 'X'.
       ENDIF.
       IF <data>-field3 = 'X'.
         <temp>-field3 = 'X'.
       ENDIF.
     ENDLOOP.
   ENDLOOP.

I think that is what you wanted.

Message was edited by: Bartlomiej Borucki

[Former Member]

So I tried this Modify Query, but it did not work 😞

IF yb01 = 'X'.    

MODIFY it_<temp>  FROM wa_<temp> TRANSPORTING yb01.
ENDIF.

it_<temp>  --> internal table

wa_<temp>  --> work area

[former_member246634]

I've edited my post and as you can see there is no modify statement and it works.

EDIT:

Look at field symbols and its performance: http://zevolving.com/2009/12/use-of-field-symbols-vs-work-area/

Message was edited by: Bartlomiej Borucki

[Former Member]

Hi Bartlomiej,

It work now.... Thanks a lot

I have adapted to my coding and change the logic and it work perfect.

Thanks once again.

[Juwin]

By 'X' in the fields, I assume, it is just a flag and you are not concerned what is the actual value in it. So, can you try to change all the X to 1 and then do a COLLECT?

Thanks,

Juwin

[Former Member]

Hi Juwin,

To be frank , I did it in same way 🙂

But requirement is of 'X', since it is flag