topic Re: Deleting Internal table based on condition in Application Development and Automation Discussions

Deleting Internal table based on condition

Former Member — Thu, 12 May 2016 07:13:01 GMT

Hi,

I am kinda stuck in the process of Deleting the Internal table which contains many duplicates.

Here is the brief overview of it.

Record: 1, Vendor Number - 00123456, Invoice Number-ABCD, Invoice Amount -$20,000, Invoice Date-12/15/2015 DTYPI: X

Record:2. Vendor Number - 00123456, Invoice Number-ABCD, Invoice Amount -$20,000, Invoice Date-12/15/2015 DTYPC: X

Record:3. Vendor Number - 00123456, Invoice Number-ABCD, Invoice Amount -$20,000, Invoice Date-12/15/2015

$20,000 - $20,000 + $20,000 = $20,000 Invoice posted

Here Internal table is having 3 records. Record 1,2 and 3.

All 3 records having same entries, but record 1 is having DTYPI and record 2 is having DTYPC.

I need to delete record 1 and record 2 comparing vendor Number, invoice number, invoice amount and invoice date are same and if record 1 having DTYPI = X and record 2 is having DTYPC = 'X', then Record 1 and record 2 need to be delete from internal table and an internal table should have Record 3 only.

I did so many loops and couldn`t able to achieve it.

Please give me an idea about this.

BR,

Re: Deleting Internal table based on condition

pranay570708 — Thu, 12 May 2016 07:24:21 GMT

Try something like below:

sort it_tab by vendor_no, inv_no, inv_amt, inv_dt.

Loop at it_tab into wa_tab1.

read table it_tab into wa_tab2 with key vendor_no = wa_tab1-vendor_no

inv_no = wa_tab1-inv_no

inv_amt = wa_tab1-inv_amt

inv_dt = wa_tab1-dt.

if sy-subrc = 0.

if wa_tab2-DTYPI = 'X' or wa_tab2-DTYPC = 'X'.

Delete it_tab from wa_tab2.

endif.

endloop.

Re: Deleting Internal table based on condition

Former Member — Thu, 12 May 2016 07:25:06 GMT

From your message itself, you can convert it to code

(Assuming the table only have 3 records as you said)

DELETE ADJACENT DUPLICATES FROM i_record

COMPARING vendor_number invoice_number invoice_amount invoice_date.

READ TABLE i_record INTO wa_record1 INDEX 1.

IF sy-subrc EQ 0.

READ TABLE i_record INTO wa_record2 INDEX 2.

IF sy-subrc EQ 0.

IF wa_record1-dtypi IS NOT INITIAL.

and wa_record2-dtypc is not initial.

DELETE i_record INDEX 1.

DELETE i_record INDEX 2.

ENDIF.

Re: Deleting Internal table based on condition

former_member291971 — Thu, 12 May 2016 07:46:25 GMT

Hi ethan,

Hope this will solve your issue for sure.

After Loop.

Delete [internal table] where DTYPI = 'X' or DTYPC = 'X'.

Thanks and Regards,

Winse Solomon.

Re: Deleting Internal table based on condition

matt — Thu, 12 May 2016 08:11:55 GMT

Your data structure is unclear. Is DTYPI a value or a fieldname?

Re: Deleting Internal table based on condition

Former Member — Thu, 12 May 2016 08:14:33 GMT

Hi Pranay,

I slightly changed the requirement.

Record: 1, Vendor Number - 00123456, Invoice Number-ABCD, Invoice Amount -$20,000, Invoice Date-12/15/2015 DTYPI: X

Record:2. Vendor Number - 00123456, Invoice Number-ABCD, Invoice Amount -$20,000, Invoice Date-12/15/2015 DTYPC: X

Record:3. Vendor Number - 00123456, Invoice Number-ABCD, Invoice Amount -$20,000, Invoice Date-12/15/2015 DTYPI: X

Record 3 is also having DTYPI = 'X'.

I will just have to delete the duplicates+ that cancel each other+, i.e. two records with opposite dtypc/i pairs. So I just need to delete those "pairs".

Re: Deleting Internal table based on condition

Former Member — Thu, 12 May 2016 08:15:45 GMT

Hi EL,

I slightly changed the requirement.

Record: 1, Vendor Number - 00123456, Invoice Number-ABCD, Invoice Amount -$20,000, Invoice Date-12/15/2015 DTYPI: X

Record:2. Vendor Number - 00123456, Invoice Number-ABCD, Invoice Amount -$20,000, Invoice Date-12/15/2015 DTYPC: X

Record:3. Vendor Number - 00123456, Invoice Number-ABCD, Invoice Amount -$20,000, Invoice Date-12/15/2015 DTYPI: X

Record 3 is also having DTYPI = 'X'.

I will just have to delete the duplicates+ that cancel each other+, i.e. two records with opposite dtypc/i pairs. So I just need to delete those "pairs".

PS: I just used 3 records as an example, there are 500+ records in the Internal table though

Regards,

Re: Deleting Internal table based on condition

Former Member — Thu, 12 May 2016 08:17:16 GMT

Hi,

DTYPI and DTYPC are field names and I used it as Flag.

Re: Deleting Internal table based on condition

prajeshdesai — Thu, 12 May 2016 08:32:55 GMT

try below,


SORT IT_TAB BY VENDOR_NO, INV_NO, INV_AMT, INV_DT.


DELETE ADJACENT DUPLICATES FROM IT_TAB COMPARING VENDOR_NO, INV_NO, INV_AMT, INV_DT.

Then write your DTYPI and DTYCI logic if required.

I'm not getting your last point, but you can try this.

Re: Deleting Internal table based on condition

schaganti1 — Thu, 12 May 2016 08:55:52 GMT

Hi Ethan,

Please check and try with the below logics ,it should work.

Logic 1.

sort internal table by vendor number invoice number invoice date dtypi.

delete adjacent duplicates comparing vendor number invoice number invoice date dtypi.

what my basic idea is it sorts the internal table in this manner.

Record: 1, Vendor Number - 00123456, Invoice Number-ABCD, Invoice Amount -$20,000, Invoice Date-12/15/2015 DTYPI: X

Record:2. Vendor Number - 00123456, Invoice Number-ABCD, Invoice Amount -$20,000, Invoice Date-12/15/2015 DTYPI: X

Record:2. Vendor Number - 00123456, Invoice Number-ABCD, Invoice Amount -$20,000, Invoice Date-12/15/2015 DTYPC: X

and deleting the one of the record of first two records , you can try this logic with any no of records.

Logic 2

And you can try with this below logic too.

move the records which have DTYPI ='X' and DTYPC = 'X' into separate another internal tables by loop.

and

sort that internal table (which has DTYPI ='X' Records ) by vendor number invoice number invoice date dtypi.

delete adjacent duplicates comparing vendor number invoice number invoice date dtypi.

after deletion and you can combine this internal table with the internal table of DTYPC = 'X' .

Regards

Sudhakar

Re: Deleting Internal table based on condition

pranay570708 — Thu, 12 May 2016 08:56:23 GMT

What i understand is that you want to delete duplicate records with same Vendor number, invoice no, invoice amount, invoice date based on DTYPI =X or DTYPC = X. As per above case, the final table should contain 2 records (one with DTYPI =X and other with DTYPC = X), correct?

If that's the case then try below:

"First delete duplicates with DTYPI

Sort it_tab by vendor_no, inv_no, inv_amt, inv_dt, DTYPI.

DELETE ADJACENT DUPLICATES FROM iit_tab

COMPARING vendor_number invoice_number invoice_amount invoice_date DTYPI.

"After that delete duplicates with DTYPC

Sort it_tab by vendor_no, inv_no, inv_amt, inv_dt, DTYPC.

DELETE ADJACENT DUPLICATES FROM iit_tab

COMPARING vendor_number invoice_number invoice_amount invoice_date DTYPC.

Re: Deleting Internal table based on condition

Former Member — Thu, 12 May 2016 09:10:15 GMT

Hi,

The dtypi and dtypc are always mutually exclusive and one of the two is always X. So IF NOT wa_int-dtypi IS INITIAL OR wa_int-dtypc IS NOT INITIAL . will always be true.

This means that now amongst the duplicates, I am just deleting all the rows with same lifnr name1 xbldt wrbtr.

Instead of this, I will just have to delete the duplicates+ that cancel each other+, i.e. two records with opposite dtypc/i pairs. I need to delete those "pairs".

But i am also confused with this.

Re: Deleting Internal table based on condition

pranay570708 — Thu, 12 May 2016 09:30:03 GMT

Hi Ethan,

What's your requirement you are not really explaining well enough. From below scenario, just let me know what's your expected final output:

Record: 1, Vendor Number - 00123456, Invoice Number-ABCD, Invoice Amount -$20,000, Invoice Date-12/15/2015 DTYPI: X

Record:2. Vendor Number - 00123456, Invoice Number-ABCD, Invoice Amount -$20,000, Invoice Date-12/15/2015 DTYPC: X

Record:3. Vendor Number - 00123456, Invoice Number-ABCD, Invoice Amount -$20,000, Invoice Date-12/15/2015 DTYPI: X

From above three records, after operation which record/records you want finally.

Re: Deleting Internal table based on condition

Former Member — Thu, 12 May 2016 09:34:09 GMT

Hi,

Record 1 and record 2 need to get delete and only record 3 should need to display

Re: Deleting Internal table based on condition

pranay570708 — Thu, 12 May 2016 09:39:34 GMT

Then, I guess you are not bothered about DTYPI/DTYPC values at all. Try using below:

Sort it_tab by vendor_no, inv_no, inv_amt, inv_dt.

DELETE ADJACENT DUPLICATES FROM iit_tab

COMPARING vendor_number invoice_number invoice_amount invoice_date.

Re: Deleting Internal table based on condition

Former Member — Thu, 12 May 2016 09:53:22 GMT

Nope.

Internal table does have the both DTYPI and DTYPC fields..

When I create a record-1 i select DTYPI and followed by same endor_number invoice_number invoice_amount invoice_date.

2nd record creation, I select DTYPC and same endor_number invoice_number invoice_amount invoice_date.

3rd Record, I will choose DTYPI and ame endor_number invoice_number invoice_amount invoice_date..

Since DTYPC = X and DTYPC = X in 1st 2 records, hence they should need to eliminate and 3rd record need to display..

Clearly DTYPI = DTYPI no deletion of record. But DTYPI = DTYPC or vice versa, need to delete.

Please have a look at the below screenshot

record number: 16204 and 16205 need to eliminate and 16206 need to display!

Re: Deleting Internal table based on condition

pranay570708 — Thu, 12 May 2016 10:07:01 GMT

I got your point, you want the record with latest record number, right? Well, you would have mentioned that earlier.

Then, You will have to include record_number in sort as well but Descending order for this field.

Sort it_tab by RECNO DESCENDING vendor_no inv_no inv_amt inv_dt.

DELETE ADJACENT DUPLICATES FROM it_tab

COMPARING vendor_number invoice_number invoice_amount invoice_date.

You will get 16206 record after this.

Re: Deleting Internal table based on condition

PeterJonker — Thu, 12 May 2016 10:10:50 GMT

If only one of the fields DTYPI or DTYPC can be filled per record then I think this code could do the trick:

sort it_tab1 by vendor_no, inv_no, inv_amt, inv_dt.

* create a copy of the table

it_tab2 = it_tab1.

* create two helpfields as counters

data: counter1 typei,

counter2 type i.

Loop at it_tab1 into wa_tab1.

clear: counter1, counter2.

loop at it_tab2 into wa_tab2 where vendor_no = wa_tab1-vendor_no

and inv_no = wa_tab1-inv_no

and inv_amt = wa_tab1-inv_amt

and inv_dt = wa_tab1-dt.

if wa_tab2-DTYPI = 'X'.

counter1 = counter1 + 1.

elseif wa_tab2-DTYPC = 'X'.

counter2 = counter2 + 1.

endif.

endloop.

if counter1 > counter2.

delete it_tab2 where vendor_no = wa_tab1-vendor_no

and inv_no = wa_tab1-inv_no

and inv_amt = wa_tab1-inv_amt

and inv_dt = wa_tab1-dt

and dtypc = 'X'.

elseif counter2 > counter1.

delete it_tab2 where vendor_no = wa_tab1-vendor_no

and inv_no = wa_tab1-inv_no

and inv_amt = wa_tab1-inv_amt

and inv_dt = wa_tab1-dt

and dtypi = 'X'.

endif.

endloop.

sort it_tab2.

delete adjacent duplicates from it_tab2.

* all the double/triple/quadruple entries will be deleted and only the entry you want is left

Re: Deleting Internal table based on condition

Former Member — Thu, 12 May 2016 10:20:38 GMT

Hi,

Try to get the Record 1 number in record 2(if you captured it).

Then delete the Record 1 and 2.

it_tab1[] = it_tab[].

it_tab2[] = it_tab[].

DELETE it_tab1 where DTYPC <> 'X'.

DELETE it_tab2 where DTYPI <> 'X'.

LOOP AT it_tab1.

READ TABLE it_tab2 WITH KEY record2_refrece = it_tab1-record1.

IF sy-subrc = 0.

DELETE it_tab1,

ENDIF.

ENDLOOP.

refresh it_tab[].

it_tab[] = it_tab1[].

Re: Deleting Internal table based on condition

former_member291971 — Thu, 12 May 2016 10:23:28 GMT

If it is a fieldname , you can use my syntax after the loop.It will Work.