Not matching records loop

[Former Member]

Hi experts,

I have the following three tables:

Source Table

Account	Functional Area	Profit Center	Amount
60000	00	10001	50,00
60000	05	20002	130,00
70000	13	30001	-450,00

Master Data Table:

Account	Functional Area Range from	Functional Area Range To	Profit Center Range from	Profit Center Range To	Virtual Account
60000		03	10001	10010	AX
60000	04	10	20001	20010	AY
70000	11	20	30000	30010	BX

Target Table

Account	Functional Area	Profit Center	Amount	Virtual Account
60000	00	10001	50,00	AX
60000	05	20002	130,00	AY
70000	13	30001	-450,00	BX

Source table is feeding Target Table. I have to fill virtual account in target table by reading the master data table.

The idea is like this:

-When the accounts are equal between source table and Master data table

-Check in a first time the functional area in source table between from and to in master data table

-Then check the profit center in source table between from and to in master data table

-Then fill virtual account

Amine

Message Part (1/2)

[Former Member]

Hi experts,

I have the following three tables:

Source Table

Account	Functional Area	Profit Center	Amount
60000	00	10001	50,00
60000	05	20002	130,00
70000	13	30001	-450,00

Master Data Table:

Account	Functional Area Range from	Functional Area Range To	Profit Center Range from	Profit Center Range To	Virtual Account
60000		03	10001	10010	AX
60000	04	10	20001	20010	AY
70000	11	20	30000	30010	BX

Target Table

Account	Functional Area	Profit Center	Amount	Virtual Account
60000	00	10001	50,00	AX
60000	05	20002	130,00	AY
70000	13	30001	-450,00	BX

Source table is feeding Target Table. I have to fill virtual account in target table by reading the master data table.

The idea is like this:

-When the accounts are equal between source table and Master data table

-Check in a first time the functional area in source table between from and to in master data table

-Then check the profit center in source table between from and to in master data table

-Then fill virtual account

Amine

Message Part (1/2)

[Former Member]

Here how I do it and it’s woking fine (but my solution is limited I will explain later why):

Data : ff ,ft, pf, pt type i.

loop at Master_Data_table into structure where account = source-account.

ff = structure-fa from.

ft = structure-ft to.

pf= structure-pc from.

pt = structure- pc to.

if source-fa >=ff and source-fa <= ft.

     if source-pc >=pf and source-pc <= pt.

     target-va = structure-va.

                     endif.

           endif.

endloop.

The issue is that some times the loop will not find any corresponding values in master data table and I would like to store them in an error table. Here an example of not matching records assmung that my master data table contains only the records above.

Source Table

Account	Functional Area	Profit Center	Amount
90000	05	10001
60000	09	10001	50,00
60000	05	30002	130,00

I tried to find a solution with the sy-subrc but in debug mode it’s always eq 0.

Thanks.

Amine

Message Part (2/2)

[former_member207873]

Hi Amine,

Only the Account no in Source table and the Master table has to be same or the entire structure?

You have given like.

loop at Master_Data_table into structure where account = source-account.

So what is Source-account? Can you give the piece of coding you have written above the loop statement?

[Former Member]

Hi

You need to move all conditions in WHERE clauses

loop at Master_Data_table into structure

     where account = source-account

               and ( fa_from <= source-fa

               and    fa_to => soure-fa )

             and   (pc_from <= source-pc

               and (pc_to => source-pc ).

      target-va = structure-va.

endloop.

if sy-subrc <> 0.

----------> ERROR

endif.

Max

[Former Member]

Hi,

Source-account is the field account from source table.

The idea of my code was to loop until find in the master data table using the key account a matching coorespondance record.

Let me know if you need more details.

Amine

[Former Member]

Hi Max,

interesting. But i have to convert the fields into i type. This is why i made this in the loop:

Data : ff ,ft, pf, pt type i.

ff = structure-fa from.

ft = structure-ft to.

pf= structure-pc from.

pt = structure- pc to

How can i manage it in your proposal?

Thanks.

Amine

[nabheetscn]

Loop at source table.

loop at Master_Data_table into structure where account = source-account.

ff = structure-fa from.

ft = structure-ft to.

pf= structure-pc from.

pt = structure- pc to.

if source-fa >=ff and source-fa <= ft.

     if source-pc >=pf and source-pc <= pt.

     target-va = structure-va.

                     endif.

           endif.

endloop.

if sy-subrc ne 0 or taget-va is initial." meaning no data matches

append error record for no data for account.

endif.

endloop.

[Former Member]

Hi Nahbeet,

Thanks for your answer.

Do you think that i may add an exit to go out the loop when a record is found?

Thanks.

Amine

[nabheetscn]

Yes...i belive so

[Former Member]

Something like this, right?

loop at Master_Data_table into structure where account = source-account.

ff = structure-fa from.

ft = structure-ft to.

pf= structure-pc from.

pt = structure- pc to.

if source-fa >=ff and source-fa <= ft.

     if source-pc >=pf and source-pc <= pt.

     target-va = structure-va.

exit.

                     endif.

           endif.

endloop.

[arivazhagan_sivasamy]

Hi,

Please try below code.

Loop at Sourcetable into wa_source

read table mastertable into wa_master with account = wa_source-account.

if sy-subrc = 0.

if wa_source-functionarea between wa_master-fromfunctionarea and wa_master-tofunctionarea.

wa_final-virtualaccount = wa_master-virtualaccount.

append wa_final to it_final.

endif.

endloop.

if you want to perform first time of functional area.

Please use at new of functionalarea or on change of wa_source-funationalarea.

Hope above code resolve your issue.

[Former Member]

Hi Ariva,

The issue of the read is that the code is going to read only the first record and not all the others.

But the idea is to loop until find a corresponding value in the master data table. If no value is found then add it to my error table.

Thanks.

Amine

[philipdavy]

Hi Amine,

Can you give us more details?

1) CASE 1

Suppose Source Table contains,

60000

70000

80000

and Master Table contains

70000

80000

90000

Then Your Result table should be :

70000

80000

and your Error Table should be:

60000

OR

2) CASE 2

RESULT TABLE

70000

80000

ERROR TABLE

60000

90000

In first case,LOOP SOURCE TABLE

                   READ TABLE MASTER TABLE INTO WA_MASTERTABLE WITH KEY ACCNT =                           ACCNT- SOURCE TABLE.

                    IF SY-SUBRC = 0.

                   RESULT TABLE OPERATIONS

                   ELSE.

                    ERROR TABLE OPERATIONS.

                    ENDIF.

                     ENDLOOP.

In second case, write one more loop.

                   loop at    master table

                   READ TABLE SOURCE_TABLE INTO WA_SOURCE TABLE WITH KEY ACCNT =                         MASTERTABLE-ACCNT

                      IF SY-SUBRC = 0.

                       RESULT TABLE OPERATIONS

                      ELSE

                        ERROR TABLE OPERATIONS.

                     ENDIF.

                     ENDLOOP.

Regards,

Philip.

[Former Member]

Hi Philip,

The three conditions have to be respected, not only the account

Thx.

Amine

[venkat_aileni]

Hi-

Try below approach:

DATA: lv_data_missing.

LOOP AT it_source INTO wa_source.

   CLEAR: wa_master.

   READ TABLE it_master INTO wa_master WITH KEY account = wa_source-account.

   IF sy-subrc = 0.

     CLEAR wa_master.

     LOOP AT it_master INTO wa_master WHERE account = wa_source-account.

       CLEAR: lv_data_missing.

       IF ( wa_source-fa >= wa_master-ff OR

            wa_source-fa <= wa_master-ft ).

       ELSE.      

         lv_data_missing = 'X'.

         WRITE: 'Error: Functional Area missing for Account', wa_source-account.

       ENDIF.

       IF ( wa_source-pf >= wa_master-pf OR

            wa_source-pf <= wa_master-pt ).

       ELSE.         

         lv_data_missing = 'X'.

       ENDIF.

       IF lv_data_missing = 'X'.

         CONTINUE.

       ELSE.

         "Append record to your final internal table

       ENDIF.

     ENDLOOP.

   ELSE.

     WRITE: / 'Error: Account', wa_source-account 'Not found'.

   ENDIF.

ENDLOOP.

-Venkat

[Former Member]

Hi Venhat,

In this piece of code:

  IF ( wa_source-fa >= wa_master-ff OR

            wa_source-fa <= wa_master-ft ).

Do you meant?

  IF ( wa_source-fa >= wa_master-ff AND

            wa_source-fa <= wa_master-ft ).

Thanks.

Amine

[venkat_aileni]

Hello Amine-

Correction! it's 'AND' and not 'OR'.

       IF ( wa_source-fa >= wa_master-ff AND

            wa_source-fa <= wa_master-ft ).

       IF ( wa_source-pf >= wa_master-pf AND

            wa_source-pf <= wa_master-pt ).

Also you can use BETWEEN too.

IF ( wa_source-fa BETWEEN wa_master-ff AND wa_master-ft ).

ENDIF.

-Venkat

[Former Member]

Thanks.

Amine

[Former Member]

Venkat,

What do you think about the follwing logic. Actually it will be more easy for me to implement and maintain:

Thx

.

loop at source into wa_source

loop at Master_Data_table into structure

     where account = source-account

               and ( fa_from <= source-fa

               and    fa_to => soure-fa )

             and   (pc_from <= source-pc

               and (pc_to => source-pc ).

if sy-subrc eq 0.

      target-va = structure-va.

else.

     append to err

endloop.

endloop.

[venkat_aileni]

Hello Amine-

Agreed! You can for sure use that logic its straight forward and with a single condition. I have written my logic with an intention of more error specific. Say suppose if there is a miss match in functional area or profit center or miss match in both you can populate/capture error.

If your requirement is to just append error table only when there is a miss match in account please go ahead with that logic.

-----------------------------------------------------------------------

SORT: sorce   BY account ASCENDING,

           master BY account ASCENDING.

loop at Master_Data_table into structure

     where account = source-account

               and ( fa_from <= source-fa

               and    fa_to => soure-fa )

             and   (pc_from <= source-pc

               and (pc_to => source-pc ).

endloop.

if sy-subrc eq 0.

      target-va = structure-va.

else.

     append to err

endif.

endloop.

-Venkat

[nabheetscn]

Just to add to Venkat code add an exit inside loop the moment you find one you skip the rest of processing.

[Former Member]

I have another question please:

How can i convert fa_from, fa_to,pc_from, pc_to to i type before the loop?

I would like that the loop considers them as type i.

In my initial proposal i made this but i don't know how to adapt it to your last version of code:

data: ff, ft, pf, pt type i.

loop at Master_Data_table into structure where account = source-account.

ff = structure-fa from.

ft = structure-ft to.

pf= structure-pc from.

pt = structure- pc to.

if source-fa >=ff and source-fa <= ft.

     if source-pc >=pf and source-pc <= pt.

     target-va = structure-va.

                     endif.

           endif.

endloop.

Thanks.

Amine

[venkat_aileni]

Hi Amine-

There is no need to convert these variables to integer type, please see below sample code for your reference:

In below code field prctr is of type character with length 10, Data Element 'PRCTR'.

TYPES: BEGIN OF t_itab,

        prctr TYPE prctr,

        END OF t_itab.

DATA: itab TYPE STANDARD TABLE OF t_itab,

       wa_itab TYPE t_itab,

       itab_temp TYPE STANDARD TABLE OF t_itab,

       wa_itab_temp TYPE t_itab.

wa_itab-prctr = '5000'.

APPEND wa_itab TO itab.

wa_itab-prctr = '6000'.

APPEND wa_itab TO itab.

wa_itab-prctr = '7000'.

APPEND wa_itab TO itab.

wa_itab-prctr = '8000'.

APPEND wa_itab TO itab.

wa_itab_temp-prctr = '1000'.

APPEND wa_itab_temp TO itab_temp.

wa_itab_temp-prctr = '2000'.

APPEND wa_itab_temp TO itab_temp.

wa_itab_temp-prctr = '3000'.

APPEND wa_itab_temp TO itab_temp.

LOOP AT itab INTO wa_itab.

   LOOP AT itab_temp INTO wa_itab_temp WHERE prctr <= wa_itab-prctr.

     WRITE: / wa_itab_temp-prctr.

   ENDLOOP.

ENDLOOP.

Output:

Please go ahead with your coding.

-Venkat