topic Re: Delete same fields in query (not the duplicate lines) in Application Development and Automation Discussions

Delete same fields in query (not the duplicate lines)

Former Member — Mon, 01 Jul 2013 10:34:19 GMT

Dear Experts,

Now i use LIPS to left outer jion VBAK to list the sales order report. I set sales orde quantity, net value, DN qty, DN amount as the fields need be to listed. After running out, i got an issue. Some of the items in sales order has been deliveried 2 or more times. EX, oder qty 30 pcs, DN1 post 20pcs, DN2 post 10pcs. Then each items has been listed as below:

Sales order item material order qty net value DN DN qty DN value

9240001 10 S0001 30 3000 810**** 20 2000

9240001 10 S0001 30 3000 811**** 10 1000

Total 60 6000 30 3000

But i want the total order qty EQ total DN qty, net value eq total Dn value.

Could any one tell me how to keep only 1 lines of "30 3000" here after sorting ? That is to say if the same order and same item shown twice or more, the order qty and net value change to be "0".

I dont want to delete the second items here, just want the oder qty and net value of item 2 to be blank.

AS below:

Sales order item material order qty net value DN DN qty DN value

9240001 10 S0001 30 3000 810**** 20 2000

9240001 10 S0001 0 0 811**** 10 1000

Total 30 3000 30 3000

Beter by coding in " END OF SELECTION", thank you very much!

Best regards,

Peter

Re: Delete same fields in query (not the duplicate lines)

Former Member — Mon, 01 Jul 2013 10:49:05 GMT

Hi ,

You will need to clear the values other than the first one.

SORT the table by vbeln and posnr.

and write the code like this.

Data : lv_flag type c length 1.

loop at itab assigning <itab_line>.

at new posnr.

lv_flag = 'X'.

endat.

if lv_flag is initial

clear : <itab_line>-order_quant.

else.

clear lv_flag.

endif.

endloop.

Condition for this code to work VBELN should be the first field of ITAB and POSNR should be the second

Re: Delete same fields in query (not the duplicate lines)

Former Member — Mon, 01 Jul 2013 10:53:31 GMT

Hi peter,

Find the below code.

sort it_final by vbeln posnr.

loop at it_final into wa_final.

wa_final-orderqty = 0.

wa_final-netvalue = 0.

at new posnr.

coninue.

endat.

modify it_final from wa_final index sy-tabix.

endloop.

Re: Delete same fields in query (not the duplicate lines)

Former Member — Mon, 01 Jul 2013 11:17:54 GMT

Hi zhpg 126,

During Loop you can assign sales doc. no and item to some variable.

suppose.

clear: gd_vbeln,gd_posnr.

Loop at it_xyz.

if gd_vbeln eq it_xyz-menge and gd_posnr eq it_xyz-dmbtr.

clear:it_output-menge ,it_output-dmbtr.

append it_output.

else.

gd_vbeln = it_xyz-menge.

gd_posnr = it_xyz-dmbtr.

append it_output.

endif.

endloop.

Re: Delete same fields in query (not the duplicate lines)

Former Member — Tue, 02 Jul 2013 02:42:37 GMT

Could you tell me more about how to define data and inner tables? I tried but failed. The data lsited are the same with before.

Re: Delete same fields in query (not the duplicate lines)

Former Member — Tue, 02 Jul 2013 06:52:26 GMT

if the previous code doesnt work, use on change instead of at new.

sort it_final by vbeln posnr.

loop at it_final into wa_final.

wa_final-orderqty = 0.

wa_final-netvalue = 0.

on change of wa_final-posnr.

coninue.

endon.

modify it_final from wa_final index sy-tabix.

endloop

Re: Delete same fields in query (not the duplicate lines)

Former Member — Tue, 02 Jul 2013 07:43:43 GMT

DATA : Begin of wa_itab,

vbeln type vbap-vbeln,

posnr type vbap-posnr,

kwmeng type vbap-kwmeng,

vbeln_vf type lips-vbeln,

posnr_vf type lips-posnr,

lfimg type lips-lfimg,

end of wa_itab.

DATA : itab like table of wa_itab.

select a~vbeln a~posnr a~kwmeng b~vbeln b~posnr b~lfimg from VBAP as A left outer join LIPS as b

on a~vbeln = b~vgbel

and a~posnr = b~vgpos.

sort itab by vbeln posnr.

loop at itab into wa_itab.

at new posnr.

continue.

endat.

clear wa_itab-kwmeng.

modify itab from wa_itab index sy-tabix.

Endloop.

This will give you your output.

Re: Delete same fields in query (not the duplicate lines)

former_member217544 — Tue, 02 Jul 2013 08:03:17 GMT

Hi,

Can you share the code that you tried with all the suggestions. We will suggest if you miss anything.

Try this logic:

loop at itab assigning <itab_line>.

if lv_flag = 'X'.

clear : <itab_line>-order_quant.

endif.

at new matnr.

lv_flag = 'X'.

endat.

at end of matnr.

clear lv_flag.

endat.

endloop.

Regards,

Swarna

Re: Delete same fields in query (not the duplicate lines)

Former Member — Tue, 02 Jul 2013 10:21:32 GMT

Hello Adapa,

Thank you for your help. But the data still can't be roll out.

I set the data as below, could you help to check where is wrong?

Constants : my_table(6) VALUE '%G00[]'.

FIELD-SYMBOLS : <T1> TYPE table.

Data: begin of T2 occurs 0,

      qty like vbap-kwmeng,

      value like vbap-netwr,

      end of t2.

Assign (my_table) to <t1>.

Check <t1> IS ASSIGNED.

Sort <t1> by vbeln posnr.

loop at <T1> into t2.

t2-qty = 0.

t2-value = 0.

on change of t2-posnr.

continue.

endon.

modify t1 from t2 index sy-tabix.

endloop.

Thanks !

Peter

Re: Delete same fields in query (not the duplicate lines)

Former Member — Tue, 02 Jul 2013 10:34:20 GMT

Dear Mohammed,

Thanks for you reply. I tried your codes, but it didn't works here.

The list are the same.

Peter

Re: Delete same fields in query (not the duplicate lines)

Former Member — Tue, 02 Jul 2013 10:36:27 GMT

Dear Swama,

Thank you for your help. I tried your codes, but i don't know how to set header data as you thought.

And it also failed.

Thanks again.

Br,

Peter

Re: Delete same fields in query (not the duplicate lines)

Former Member — Tue, 02 Jul 2013 10:38:08 GMT

Dear Kryptonite,

Thank you! I tried codes as you list. But it still not works, could you provide the codes more completely for me? Thank you very much!

br,

Peter

Re: Delete same fields in query (not the duplicate lines)

former_member191562 — Tue, 02 Jul 2013 11:51:56 GMT

Hi Z ....,

For your requirement, I am assuming that you dont need any other data from VBAP & LIPS except the delivered items and their total quantity.

If thats what you want, you can do the following with the SELECT query.

TYPES: BEGIN OF t_table,
         vbeln TYPE vbak-vbeln,
         posnr TYPE vbap-posnr,
         matnr TYPE vbap-matnr,
         kwmeng TYPE vbap-kwmeng,
         lfimg TYPE lips-lfimg,
        END OF t_table.
DATA: i_table TYPE STANDARD TABLE OF t_table.

SELECT a~vbeln a~posnr a~matnr a~kwmeng SUM( b~lfimg )
   INTO TABLE i_table
   FROM VBAP AS A LEFT outer JOIN LIPS AS b
   ON a~vbeln = b~vgbel
   AND a~posnr = b~vgpos
   GROUP BY a~vbeln a~posnr a~matnr a~kwmeng.

Warning: This query might take a little bit more time than querying out all the entries. But the difference will be miniscule when the data is small.

Thanks,

Yuvaraj S

Re: Delete same fields in query (not the duplicate lines)

Former Member — Wed, 03 Jul 2013 02:58:05 GMT

Hi Yuvaraj,

Sorry, i can't do that since i need the DN number to be listed.

Thanks!

Br,

Peter

Re: Delete same fields in query (not the duplicate lines)

former_member217544 — Wed, 03 Jul 2013 05:02:47 GMT

Hi,

I dont understand what header data you are unable to sett? I didnt mention any header data in that logic.

Regards,

Swarna

Re: Delete same fields in query (not the duplicate lines)

Former Member — Wed, 03 Jul 2013 06:13:32 GMT

Hi Swama,

What I want to say is the Define data.

Such as :

DATA : Begin of itab occurs 0,
              vbeln type vbap-vbeln,
              posnr   type vbap-posnr,
              kwmeng type vbap-kwmeng,
              netwr   type vbap-netwr,
        end of itab.
DATA : wa_itab like itab.

Until now, I tried all the ways you listed, but none of them can get the answer i want. Could you help tp figure out the rignt codes?

Thank you!

Regards,

Peter

Re: Delete same fields in query (not the duplicate lines)

former_member191562 — Wed, 03 Jul 2013 06:56:20 GMT

Hi Z....,

Since you need data from both the tables for your processing, I would suggest to go with two separate queries using for all entries.

SELECT vbeln posnr matnr kwmeng FROM vbak
   INTO TABLE i_vbap
   FROM vbap
   WHERE vbeln IN so_vbeln.
   
 SELECT vbeln posnr matnr lfimg
   INTO TABLE i_lips 
   FROM lips
   FOR ALL ENTRIES IN i_vbap
   WHERE vgbel = i_vbap-vbeln
    AND  vgpos = i_vbap-posnr.
   
 LOOP AT i_vbap ASSIGNING <fs_vbap>.
   LOOP AT i_lips ASSIGNING <fs_lips>
                  WHERE vgbel = <fs_vbap>-vbeln
                   AND  vgpos = <fs_vbap>-posnr.
 * Build Another table if required here    
 * or do the required processing over here.
   ENDLOOP.
 ENDLOOP.

This will work for your requirement.

Please dont expect people to post the entire solution here. Take a hint from here and apply your own ideas to solve the problem.

-Yuvaraj S

Re: Delete same fields in query (not the duplicate lines)

matt — Wed, 03 Jul 2013 07:37:02 GMT

It is a BAD idea to use an FAE instead of a Join. In most cases, FAE is worse performing than Join. That FAE is better is a myth that has been debunked many times on this site. Please do not promote this myth.

And you haven't checked that i_vbap isn't empty! Thus demonstrating that not only is FAE worse performing, but it leads to programming errors.

However, I agree that enough information has been given now for a solution, so I am locking the thread.