08-28-2013 9:24 AM
Dear Experts,
Internal table date will be like this
Material No. Date Quantity
10001010 28.08.2013 10
10001810 28.08.2012 12
based on this internal table data i need to select next maximum date based on material no from mseg
example:
if data base like this
Material No. Date Quantity
10001010 28.08.2013 10
10001010 20.07.2013 5
10001010 10.04.2013 2
10001810 28.08.2012 12
10001810 21.08.2012 1
10001810 2.07.2012 21
i want data like this
Material No. Date Quantity
10001010 20.07.2013 5
10001810 21.08.2012 1
How we can get this values using select statement
09-30-2013 3:59 AM
Hi Ganesh,
You try like this
types : begin of ty_mkpf_mseg, " MKPF - Header: Material Document & MSEG Document Segment: Material
mblnr type mkpf-mblnr, " Material doc. no
mjahr type mkpf-mjahr, " Material Document Year
budat type mkpf-budat, " Posting Date in the Document
cpudt type mkpf-cpudt, " Day On Which Accounting Document Was Entered
zeile type mseg-zeile, " Mat. Doc. Item no.
bwart type mseg-bwart, " Movement type
matnr type mseg-matnr, " Material No.
werks type mseg-werks, " Plant
shkzg type mseg-shkzg, " Amount Indicator
dmbtr type mseg-dmbtr, " Amount
menge type mseg-menge, " Quantity
meins type mseg-meins, " Unit of measure
aufnr type mseg-aufnr, " Order No.
kdauf type mseg-kdauf, " Sales Order Number
end of ty_mkpf_mseg,
Data : it_mkpf type table of ty_mkpf_mseg,
it_mkpf_pp type table of ty_mkpf_mseg,
selection-screen : begin of block b1 with frame title text-001.
parameters : p_date type sy-datum obligatory.
selection-screen : end of block b1.
select a~mblnr
a~mjahr
a~budat
a~cpudt
b~zeile
b~bwart
b~matnr
b~werks
b~shkzg
b~dmbtr
b~menge
b~meins
b~aufnr
b~kdauf
from mkpf as a inner join mseg as b
on a~mblnr eq b~mblnr and a~mjahr eq b~mjahr
into table it_mkpf
where a~cpudt = p_date.
select a1~mblnr
a1~mjahr
a1~budat
a1~cpudt
b1~zeile
b1~bwart
b1~matnr
b1~werks
b1~shkzg
b1~dmbtr
b1~menge
b1~meins
b1~aufnr
b1~kdauf
from mkpf as a1 inner join mseg as b1
on a1~mblnr eq b1~mblnr and a1~mjahr eq b1~mjahr
into table it_mkpf_pp
for all entries in it_mkpf
where matnr = it_mkpf-matnr
and a1~cpudt = ( select max( c~cpudt )
from mkpf as c inner join mseg as d
on c~mblnr eq d~mblnr and c~mjahr eq d~mjahr
where d~matnr = it_mkpf-matnr
and c~cpudt lt it_mkpf-cpudt).
08-28-2013 3:09 PM
Hi,
first extract your data, and in second step find the good one.
The big loose of time in ABAP is the SELECT statement, so always perform simple and faster SELECT statement.
After you could make a SORT and a READ on the internal table.
MSEG is one of the biggest table of SAP, don't try to make SORT, MAX, GROUP BY ... on it
regards
Fred
08-28-2013 3:33 PM
Hello
Select everything in the correct filters, then use the loop on the internal table, but first sort the data by date and material.
Inside the loop you can use logic to store the date and the material into variables and compare with the next record in loop to check the maximum date.
Please read about Sort:
http://help.sap.com/saphelp_470/helpdata/pt/fc/eb3800358411d1829f0000e829fbfe/content.htm
And read about at new in loop.
http://help.sap.com/saphelp_470/helpdata//EN/fc/eb381a358411d1829f0000e829fbfe/content.htm
LOOP AT <itab>.
AT FIRST. ... ENDAT.
AT NEW <f1>. ...... ENDAT.
AT NEW <f2 >. ...... ENDAT.
.......
<single line processing>
.......
AT END OF <f2>. ... ENDAT.
AT END OF <f1>. ... ENDAT.
AT LAST. .... ENDAT.
ENDLOOP.
The innerm
08-28-2013 3:34 PM
Hi,
Try with below method
REPORT ZGET_SECOND_VALUE.
TYPES : BEGIN OF TP_MSEG,
MATNR TYPE MATNR,
DATE TYPE SY-DATUM,
QTY TYPE MENGE_D,
END OF TP_MSEG,
BEGIN OF TP_MAKT,
MATNR TYPE MATNR,
END OF TP_MAKT.
DATA : GT_MSEG TYPE STANDARD TABLE OF TP_MSEG,
GS_MSEG TYPE TP_MSEG,
GT_MSEG1 TYPE STANDARD TABLE OF TP_MSEG,
GS_MSEG1 TYPE TP_MSEG,
GT_MAKT TYPE STANDARD TABLE OF TP_MAKT,
GS_MAKT TYPE TP_MAKT.
DATA : GV_CNT TYPE I.
" select data from mseg into gt_mseg .
" select matnr from makt for all entries in gt_mseg
LOOP AT GT_MAKT INTO GS_MAKT.
CLEAR : GV_CNT.
LOOP AT GT_MSEG INTO GS_MSEG WHERE MATNR = GS_MSEG-MATNR.
GV_CNT = GV_CNT + 1.
IF GV_CNT = 3.
EXIT.
ELSE.
APPEND GS_MSEG TO GT_MSEG1.
ENDIF.
CLEAR : GS_MSEG, GS_MSEG1.
ENDLOOP.
CLEAR : GS_MAKT.
ENDLOOP.
SORT GT_MSEG1 BY MATNR ASCENDING DATE DESCENDING.
DELETE ADJACENT DUPLICATES FROM GT_MSEG1 COMPARING MATNR.
Regards,
Zafar
08-29-2013 3:53 AM
Hi Ganesh,
I am gonna use 3 internal tables for your program with same structure.
select statement and values into it_table1.
it_table1 have the value (sorted by material Number ascending and date descending):
Material No. Date Quantity
10001010 28.08.2013 10
10001010 20.07.2013 5
10001010 10.04.2013 2
10001810 28.08.2012 12
10001810 21.08.2012 1
10001810 2.07.2012 21
it_table2[] = it_table1[].
In this case it_table2 has same as it_table1.
delete adjacent duplicates from it_table2 comparing material number.
IT_TABLE2:
Material No. Date Quantity
10001010 28.08.2013 10
10001810 28.08.2012 12
Loop at it_table2 into wa_table2.
read it_table1 into wa_table1 with key material number eq material number date ne wa_table2-date.
if sy-subrc eq 0.
append wa_table1 to it_table3.
endif.
ENdloop.
Free: it_table2.
IT_TABLE3:
Material No. Date Quantity
10001010 20.07.2013 5
10001810 21.08.2012 1
So you have it_table1 with all values and it_table3 with second values. if you need you can use it_table2 for 1st values in your program for future validations.
Note: if there is no second record for a material number then it will be skipped.
With Regards,
Giriesh M
08-29-2013 5:03 AM
Hi Giriesh,
My requirement not to get next maximum number from internal table.
I need to get next maximum number form database table from mseg...
08-29-2013 5:45 AM
Hello Ganesh .
plaese mention what is the condition you want to find the data in internal table.
Material No. Date Quantity
10001010 28.08.2013 10
10001010 20.07.2013 5
10001010 10.04.2013 2
10001810 28.08.2012 12
10001810 21.08.2012 1
10001810 2.07.2012 21
i want data like this
Material No. Date Quantity
10001010 20.07.2013 5
10001810 21.08.2012 1
from 10001810 material you want
10001810 21.08.2012 1
from 10001010 material you want
10001010 20.07.2013 5
even though other post but unable get the condition where as
10001010 material qty 2 is their why you want 5?
08-29-2013 8:05 AM
I have a feeling that this can be done using subquery and aggregate functions.
What "Date" field in MSEG are you referring to? Most relevant date fields are in header table MKPF.
Thomas
08-29-2013 9:14 AM
You are right sub-queries can do the job (But Beware on performance)
Sample:
REPORT z_nextrecord.
TYPES :
BEGIN OF l_mseg,
matnr TYPE mseg-matnr,
cpudt TYPE mseg-cpudt_mkpf,
menge TYPE mseg-menge,
END OF l_mseg.
DATA: gt_keys TYPE STANDARD TABLE OF l_mseg,
gt_mseg TYPE STANDARD TABLE OF l_mseg.
SELECT matnr cpudt_mkpf menge
INTO TABLE gt_keys
FROM mseg UP TO 10 ROWS.
SORT gt_keys BY matnr.
DELETE ADJACENT DUPLICATES FROM gt_keys COMPARING matnr.
CHECK gt_keys[] IS NOT INITIAL.
SELECT matnr cpudt_mkpf menge
INTO TABLE gt_mseg
FROM mseg
FOR ALL ENTRIES IN gt_keys
WHERE matnr = gt_keys-matnr
AND cpudt_mkpf = ( SELECT MIN( cpudt_mkpf ) FROM mseg
WHERE matnr = gt_keys-matnr AND cpudt_mkpf GT gt_keys-cpudt ) .
SORT gt_mseg BY matnr.
BREAK-POINT.
NB: I used fields from MKPF copied into MSEG as of Note 1516684 - MKPF fields added to MSEG - Performance optimization, else JOIN between MSEG and MKPF would be required... (Fields CPUDT and BUDAT were copied into MSEG)
Regards,
Raymond
08-29-2013 8:38 AM
HI,
i would select only the entries up to a specific date. I think for you it should be sy-datum.
Then you can easily sort by date matnr.
BR
Robert
09-30-2013 3:59 AM
Hi Ganesh,
You try like this
types : begin of ty_mkpf_mseg, " MKPF - Header: Material Document & MSEG Document Segment: Material
mblnr type mkpf-mblnr, " Material doc. no
mjahr type mkpf-mjahr, " Material Document Year
budat type mkpf-budat, " Posting Date in the Document
cpudt type mkpf-cpudt, " Day On Which Accounting Document Was Entered
zeile type mseg-zeile, " Mat. Doc. Item no.
bwart type mseg-bwart, " Movement type
matnr type mseg-matnr, " Material No.
werks type mseg-werks, " Plant
shkzg type mseg-shkzg, " Amount Indicator
dmbtr type mseg-dmbtr, " Amount
menge type mseg-menge, " Quantity
meins type mseg-meins, " Unit of measure
aufnr type mseg-aufnr, " Order No.
kdauf type mseg-kdauf, " Sales Order Number
end of ty_mkpf_mseg,
Data : it_mkpf type table of ty_mkpf_mseg,
it_mkpf_pp type table of ty_mkpf_mseg,
selection-screen : begin of block b1 with frame title text-001.
parameters : p_date type sy-datum obligatory.
selection-screen : end of block b1.
select a~mblnr
a~mjahr
a~budat
a~cpudt
b~zeile
b~bwart
b~matnr
b~werks
b~shkzg
b~dmbtr
b~menge
b~meins
b~aufnr
b~kdauf
from mkpf as a inner join mseg as b
on a~mblnr eq b~mblnr and a~mjahr eq b~mjahr
into table it_mkpf
where a~cpudt = p_date.
select a1~mblnr
a1~mjahr
a1~budat
a1~cpudt
b1~zeile
b1~bwart
b1~matnr
b1~werks
b1~shkzg
b1~dmbtr
b1~menge
b1~meins
b1~aufnr
b1~kdauf
from mkpf as a1 inner join mseg as b1
on a1~mblnr eq b1~mblnr and a1~mjahr eq b1~mjahr
into table it_mkpf_pp
for all entries in it_mkpf
where matnr = it_mkpf-matnr
and a1~cpudt = ( select max( c~cpudt )
from mkpf as c inner join mseg as d
on c~mblnr eq d~mblnr and c~mjahr eq d~mjahr
where d~matnr = it_mkpf-matnr
and c~cpudt lt it_mkpf-cpudt).
09-30-2013 7:15 AM
I thought I was the only one thinking subquery to solve it. Raymond's solution looks good while Ramesh's seems quite odd with join, FAE and subquery in the same statement.