Delete same fields in query (not the duplicate lines)

[Former Member]

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

[Former Member]

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

[Former Member]

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.

[Former Member]

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.

[Former Member]

Hi

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

[Former Member]

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.

[former_member217544]

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

[Former Member]

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

[Former Member]

Dear Mohammed,

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

The list are the same.

Br

Peter

[Former Member]

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

[former_member217544]

Hi,

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

Regards,

Swarna

[Former Member]

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

[Former Member]

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.

[Former Member]

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

[former_member191562]

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

[Former Member]

Hi Yuvaraj,

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

Thanks!

Br,

Peter

[former_member191562]

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

[matt]

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.