Deleting Internal table based on condition

[Former Member]

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,

EH

[Former Member]

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,

EH

[pranay570708]

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.

[Former Member]

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".

[pranay570708]

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.

[Former Member]

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.

[pranay570708]

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.

[Former Member]

Hi,

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

[pranay570708]

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.

[Former Member]

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!

[pranay570708]

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.

[PeterJonker]

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

[Former Member]

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.

   ENDIF.

ENDIF.

[Former Member]

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,

EH

[schaganti1]

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

[former_member291971]

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.

[matt]

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

[Former Member]

Hi,

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

[prajeshdesai]

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.

[former_member291971]

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

[Former Member]

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[].