Information from multiple tables

[Former Member]

Hi experts,

Let’s say i have 3 Tables. I want to compare and change the information contained in Table1 regarding the information contained in both Table 2 and Table 3.

The information that interests me is 1 , and 0 only if no value 1 was found for a material. Example:

Table 1:

MATERIAL	VALUE
AAA	0
BBB	0
CCC	0

Table 2:

MATERIAL	VALUE
AAA	1
BBB	0
CCC	0

Table :

MATERIAL	VALUE
AAA	0
BBB	1
CCC	0

What I expect is that Table 1 will be as following:

MATERIAL	VALUE
AAA	1 (from Table1)
BBB	1 (from Table3)
CCC	0

My BW tables will contain many thousands of data, i am afraid about performance issues.

Do I have to use imbricated loops, or it’s better to use read tables?

Thanks for your support.

Amine

[Former Member]

Read itable2 with key material = <result_fields>-material binary search transporting no fields.

If sy-subrc = 0.

Loop at itable 2 from sy-tabix into str2.

If str2-value = ‘1’.

<result_fields>-value = ‘1’.

Endif.

Endloop.

Endif.

Read itable3 with key material = <result_fields>-material binary search transporting no fields.

If sy-subrc = 0.

Loop at itable 3 from sy-tabix into str3.

If str3-value = ‘1’.

<result_fields>-value = ‘1’.

Endif.

Endloop.

Endif.

[Former Member]

hello,

your requirement needs this logic

sort itab1 by material.

sort itab2 by material.

sort itab3 by material.

loop at itab1 into wa_itab1

read table itab2 into wa_itab2 with key material = wa_itab1.

if sy-subrc = 0 and wa_itab2-value not initial.

  wa_itab1-value = wa_itab2-value.

  modify itab1 from wa_itab1 transporting value.

endif.

read table itab3 into wa_itab3 with key material = wa_itab1.

if sy-subrc = 0 and wa_itab3-value not initial.

  wa_itab1-value = wa_itab3-value.

  modify itab1 from wa_itab1 transporting value.

endif.

endloop.

best regards,

swanand

[Former Member]

Please tell me you're using hashed tables!  None of his BINARY SEARCH or SORT itab BY whatever needs to happen.

Use this code:

* Move everything from table 2 to table 1.

INSERT LINES OF itab2 INTO TABLE itab1.

LOOP AT itab3 ASSIGNING <line3>.

* Check if the table 3 row exists in table 1.

  READ TABLE itab1 WITH KEY material = <line3>-material

    TRANSPORTING NO FIELDS.

* If the table 3 row doesn't exist in table 1, add it.

  IF sy-subrc <> 0.

    INSERT <line3> INTO TABLE itab1.

* If the table 3 row already exists in table 1, and it

* has value 1, modify table 1 to also have that value.

  ELSEIF sy-subrc = 0 AND <line3>-value = 1.

    MODIFY itab1 FROM <line3>.

  ENDIF.

ENDLOOP.

Simple enough, right?

[former_member220538]

Hi Amine,

Here  tab1,tab2 and tab3 are the tables and <x_tab1> is a field symbol of type table1.

x_tab2 and w_tab3 are workareas.

SORT tab1 BY material ASCENDING.
SORT tab2 BY material ASCENDING.
SORT tab3 BY material ASCENDING.

LOOP AT tab1 ASSIGNING <x_tab1>.
  READ TABLE tab2 INTO w_tab2 WITH KEY <x_tab1>-material.

  IF sy-subrc EQ 0.
       READ TABLE tab3 INTO w_tab3 WITH KEY <x_tab1>-material.

       IF sy-subrc EQ 0.
            CHECK w_tab2-value EQ 1 OR w_tab3-value EQ 1.
            <x_tab1>-value = 1.

       ENDIF.

  ENDIF.
ENDLOOP.

Regards,

Jeffin

[former_member209120]

Hi amine lamkaissi,

Try like this

TYPES : BEGIN OF ty_table,
         a(4) TYPE c,
         b TYPE i,
         END OF ty_table.


DATA : it_1 TYPE TABLE OF ty_table,
        it_2 TYPE TABLE OF ty_table,
        it_3 TYPE TABLE OF ty_table,
        wa_1 TYPE ty_table,
        wa_2 TYPE ty_table,
        wa_3 TYPE ty_table.



wa_1-a = 'AAA'. wa_1-b = ''. APPEND wa_1 TO it_1. CLEAR wa_1.
wa_1-a = 'BBB'. wa_1-b = ''. APPEND wa_1 TO it_1. CLEAR wa_1.
wa_1-a = 'CCC'. wa_1-b = ''. APPEND wa_1 TO it_1. CLEAR wa_1.

wa_2-a = 'AAA'. wa_2-b = '1'. APPEND wa_2 TO it_2. CLEAR wa_2.
wa_2-a = 'BBB'. wa_2-b = '0'. APPEND wa_2 TO it_2. CLEAR wa_2.
wa_2-a = 'CCC'. wa_2-b = '0'. APPEND wa_2 TO it_2. CLEAR wa_2.

wa_3-a = 'AAA'. wa_3-b = '0'. APPEND wa_3 TO it_3. CLEAR wa_3.
wa_3-a = 'BBB'. wa_3-b = '1'. APPEND wa_3 TO it_3. CLEAR wa_3.
wa_3-a = 'CCC'. wa_3-b = '0'. APPEND wa_3 TO it_3. CLEAR wa_3.


SORT : it_1 BY a,
        it_2 BY a,
        it_3 BY a.


LOOP AT it_1 INTO wa_1.

   READ TABLE it_2 INTO wa_2 WITH KEY a = wa_1-a BINARY SEARCH.

   IF sy-subrc = 0.
     IF wa_2-b IS NOT INITIAL.
       wa_1-b = wa_1-b + wa_2-b.
     ENDIF.
   ENDIF.
   READ TABLE it_3 INTO wa_3 WITH KEY a = wa_1-a BINARY SEARCH.
   IF sy-subrc = 0.
     IF wa_3-b IS NOT INITIAL.
       wa_1-b = wa_1-b + wa_3-b.
     ENDIF.
   ENDIF.
   MODIFY it_1 FROM wa_1 TRANSPORTING b.
   CLEAR wa_1.
ENDLOOP.


LOOP AT it_1 INTO wa_1.

   WRITE: / wa_1-a, wa_1-b.
   CLEAR wa_1.
ENDLOOP.

[Former Member]

All mentioned above code is good. but if value 0 and non 0 is Ok to your next process.

Don`t need sort the three itab.

loop gt_itab2 into gwa_itab2.

     collect gwa_itab2 into gt_itab1.

endloop.

loop gt_itab3 into gwa_itab3.

     collect gwa_itab3 into gt_itab1.

endloop.

Next process, just judge the value.

if gwa_itab1-value eq 0.

...

elseif.

...

endif.

Regards,

Archer

[Former Member]

Hi,,

Try like this..

TYPES: BEGIN OF ty_zzz,

       material(3) TYPE c,

       value     TYPE i,

      END OF ty_zzz.

DATA: t_zz1 TYPE TABLE OF ty_zzz,

      t_zz2 TYPE TABLE OF ty_zzz,

      t_zz3 TYPE TABLE OF ty_zzz,

      x_zz TYPE ty_zzz.

LOOP AT t_zz3 INTO x_zz.

  MODIFY t_zz1 FROM x_zz TRANSPORTING value WHERE material = x_zz-material.

ENDLOOP.



LOOP AT t_zz2 INTO x_zz.

   MODIFY t_zz1 FROM x_zz TRANSPORTING value WHERE material = x_zz-material.

ENDLOOP.

[Former Member]

Thanks to all of you guys.

Very interesting approachs.

Amine