topic Re: Not matching records loop in Application Development and Automation Discussions

Not matching records loop

Former Member — Wed, 20 Nov 2013 17:03:41 GMT

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)

Re: Not matching records loop

Former Member — Wed, 20 Nov 2013 17:04:51 GMT

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.

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)

Re: Not matching records loop

arivazhagan_sivasamy — Wed, 20 Nov 2013 17:27:47 GMT

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.

Re: Not matching records loop

former_member207873 — Wed, 20 Nov 2013 17:31:47 GMT

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?

Re: Not matching records loop

Former Member — Wed, 20 Nov 2013 17:38:06 GMT

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

Re: Not matching records loop

Former Member — Wed, 20 Nov 2013 18:52:56 GMT

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

Re: Not matching records loop

Former Member — Wed, 20 Nov 2013 18:56:21 GMT

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

Re: Not matching records loop

Former Member — Wed, 20 Nov 2013 18:59:17 GMT

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

Re: Not matching records loop

philipdavy — Thu, 21 Nov 2013 11:29:17 GMT

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

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.

Re: Not matching records loop

nabheetscn — Thu, 21 Nov 2013 11:36:42 GMT

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.

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.

Re: Not matching records loop

venkat_aileni — Thu, 21 Nov 2013 12:05:06 GMT

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

Re: Not matching records loop

Former Member — Thu, 21 Nov 2013 12:05:49 GMT

Hi Philip,

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

Thx.

Amine

Re: Not matching records loop

Former Member — Thu, 21 Nov 2013 12:06:58 GMT

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

Re: Not matching records loop

nabheetscn — Thu, 21 Nov 2013 12:07:56 GMT

Yes...i belive so

Re: Not matching records loop

Former Member — Thu, 21 Nov 2013 13:22:30 GMT

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

Re: Not matching records loop

Former Member — Thu, 21 Nov 2013 13:33:02 GMT

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.

endloop.

Re: Not matching records loop

venkat_aileni — Thu, 21 Nov 2013 13:40:07 GMT

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

Re: Not matching records loop

Former Member — Thu, 21 Nov 2013 13:48:00 GMT

Thanks.

Amine

Re: Not matching records loop

Former Member — Thu, 21 Nov 2013 13:59:20 GMT

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.

Re: Not matching records loop

venkat_aileni — Thu, 21 Nov 2013 14:21:15 GMT

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