11-13-2007 2:50 PM
Hi all,
I have an internal table sorted by customer and business area
e.g.
kunnr gsber value value2
10000 aa001 30 50
10000 aa001 20 30
10000 aa001 30 90
10000 bb001 10 80
10000 bb001 40 30
10003 aa001 30 40
10005 bb001 80 60
10005 cc001 20 90
10005 cc001 40 20
etc......
Needs to add the value for kunnr and gsber
and select the highest for value2
like:-
10000 aa001 80 90
10000 bb001 50 80
10003 aa001 30 40
10005 bb001 80 60
10005 cc001 40 90
etc......
Can you please help me do this. I know i will need to loop through table checking highest value and adding it with some ATs.
Thanks
11-13-2007 3:46 PM
Common steve,
use collect statement instead of append. they will be automatically added.
if you do not want to change look of this table, move thei data into another table by looping and using collect instead of append to other table.
and then follow usual procedure to get highest,
I hope is shud solve your problem
CHEERS Dude.
11-13-2007 2:54 PM
u need to move data to separate internal table and use at end of value2 for achieving one result...and sort by value descending to achieve second one....
11-13-2007 3:06 PM
i cant understand how this would work..... please explain
I was thinking of joining the customer and business area together forming a new key and using that with AT NEW.
e.g.
1000AA001
1000BB001
1001AA001
etc
How does this idea sound? Please tell me if this is not the best way.
Message was edited by:
Steve Trillo
11-13-2007 3:23 PM
see this program......
it also does the same......but u need to change the fieldnames and add additional fields as per ur requirement....
functionalitywise it is the same....!!!!!
in below program....
itab is source table and itab1 is final table....
for same var1....var2 is sum whereas var3 is highest value....!!!!
********************************************************
data : begin of itab occurs 0,
var1 type i,
var2 type i,
var3 type i,
end of itab.
data : begin of itab1 occurs 0,
var1 type i,
var2 type i,
var3 type i,
end of itab1.
data : begin of itab2 occurs 0,
var1 type i,
var2 type i,
end of itab2.
data : begin of itab3 occurs 0,
var1 type i,
var3 type i,
end of itab3.
data : begin of itab4 occurs 0,
var1 type i,
var2 type i,
var3 type i,
end of itab4.
itab-var1 = 1.
itab-var2 = 3.
itab-var3 = 5.
append itab.
clear itab.
itab-var1 = 1.
itab-var2 = 3.
itab-var3 = 4.
append itab.
clear itab.
itab-var1 = 2.
itab-var2 = 3.
itab-var3 = 5.
append itab.
clear itab.
itab-var1 = 2.
itab-var2 = 3.
itab-var3 = 6.
append itab.
clear itab.
itab-var1 = 2.
itab-var2 = 3.
itab-var3 = 8.
append itab.
clear itab.
itab-var1 = 3.
itab-var2 = 3.
itab-var3 = 5.
append itab.
clear itab.
sort itab by var1.
loop at itab.
at end of var1.
itab2-var1 = itab-var1.
sum.
itab2-var2 = itab-var2.
append itab2.
clear itab2.
endat.
endloop.
itab4[] = itab[].
sort itab4 by var1 var3 descending.
delete adjacent duplicates from itab4 comparing var1.
loop at itab4.
itab3-var1 = itab4-var1.
itab3-var3 = itab4-var3.
append itab3.
clear itab3.
endloop.
delete adjacent duplicates from itab comparing var1.
loop at itab.
itab1-var1 = itab-var1.
read table itab2 with key var1 = itab-var1.
if sy-subrc = 0.
itab1-var2 = itab2-var2.
endif.
read table itab3 with key var1 = itab-var1.
if sy-subrc = 0.
itab1-var3 = itab3-var3.
endif.
append itab1.
clear itab1.
endloop.
loop at itab1.
write 😕 itab1-var1,itab1-var2,itab1-var3.
endloop.
*****************************************************\
Regards
vasu
11-13-2007 3:34 PM
Thanks for your help but your program doesnt do what I want.
What your program does is the first bit.......
Let me change the question to make it easier so I can concentrate on the bit I am having trouble getting my head around:-
I have an internal table sorted by customer and business area
e.g.
kunnr gsber value
10000 aa001 30
10000 aa001 20
10000 aa001 30
10000 bb001 10
10000 bb001 40
10003 aa001 30
10005 bb001 80
10005 cc001 20
10005 cc001 40
etc......
Needs to add the value and summarised by kunnr and gsber
like:-
10000 aa001 80
10000 bb001 50
10003 aa001 30
10005 bb001 80
10005 cc001 40
etc......
I am having problems summarising the data for the 2 columns customer number and business area.
It would be very simple if it was just for 1 column - customer.
Understand?
Thanks
11-13-2007 3:41 PM
Hi,
Check this
sort item by kunnr gsber.
clear : v_kflg, v_gflg.
loop at itab.
at end of kunnr.
move 'Y' to v_kflg.
endat.
at end of gsber.
move 'Y' to v_gflg.
endat.
v_value = v_value + itab-value.
if v_kflg 'Y' or v_gflg = 'Y'.
move corresponding itab to itab1.
move v_value to itab1-value.
apppend itab1.
clear : v_value, v_kflg, v_gflg.
endif.
endloop.
a®
11-13-2007 3:14 PM
Hi,
sort internal table on customer business area and value2 and use following code.
loop at it_data.
v_value = it_data-value + v_value.
v_value2 = it_data-value2.
at end of gsber.
it_data1-kunnr = it_data-kunnr.
it_data1-gsber = it_data-gsber.
it_data1-value = v_value.
it_data1-value2 = v_value2.
append it_data1.
clear : v_value, v_value2.
endat.
endloop.
Regards,
Naren
11-13-2007 3:45 PM
Hi Steve,
Here ends your problem... i've replicated your data into my sample program check this...
REPORT test.
DATA : BEGIN OF itab OCCURS 0,
kunnr TYPE char10,
gsber TYPE char10,
value(2) TYPE p DECIMALS 0,
END OF itab.
DATA : itab2 LIKE itab OCCURS 0 WITH HEADER LINE.
itab-kunnr = '10000'.
itab-gsber = 'aa001'.
itab-value = 30.
APPEND itab.
itab-kunnr = '10000'.
itab-gsber = 'aa001'.
itab-value = 20.
APPEND itab.
itab-kunnr = '10000'.
itab-gsber = 'aa001'.
itab-value = 30.
APPEND itab.
itab-kunnr = '10000'.
itab-gsber = 'bb001'.
itab-value = 10.
APPEND itab.
itab-kunnr = '10000'.
itab-gsber = 'bb001'.
itab-value = 40.
APPEND itab.
itab-kunnr = '10003'.
itab-gsber = 'aa001'.
itab-value = 30.
APPEND itab.
itab-kunnr = '10005'.
itab-gsber = 'bb001'.
itab-value = 80.
APPEND itab.
itab-kunnr = '10005'.
itab-gsber = 'cc001'.
itab-value = 20.
APPEND itab.
itab-kunnr = '10005'.
itab-gsber = 'cc001'.
itab-value = 40.
APPEND itab.
LOOP AT itab.
COLLECT itab INTO itab2.
ENDLOOP.
SORT itab2 BY kunnr value DESCENDING.
LOOP AT itab2.
WRITE 😕 itab2-kunnr, itab2-gsber, itab2-value.
ENDLOOP.
11-13-2007 3:46 PM
Common steve,
use collect statement instead of append. they will be automatically added.
if you do not want to change look of this table, move thei data into another table by looping and using collect instead of append to other table.
and then follow usual procedure to get highest,
I hope is shud solve your problem
CHEERS Dude.
11-13-2007 3:56 PM
Yes that would work too if I just wanted to add the 3rd column.
Now lets introduce the 4th column which I need to work out which is the highest value. The collect would not work then as it would just sum the value for the 4th column.
Phew.......... so thats where my problem arrises............
11-13-2007 4:08 PM
Ok..steve again I've modified..check this...it's working..added 4th column as well
REPORT test.
DATA : BEGIN OF itab OCCURS 0,
kunnr TYPE char10,
gsber TYPE char10,
value(2) TYPE p DECIMALS 0,
value2(2) TYPE p DECIMALS 0,
END OF itab.
DATA : itab2 LIKE itab OCCURS 0 WITH HEADER LINE.
itab-kunnr = '10000'.
itab-gsber = 'aa001'.
itab-value = 30.
itab-value2 = 50.
APPEND itab.
itab-kunnr = '10000'.
itab-gsber = 'aa001'.
itab-value = 20.
itab-value2 = 30.
APPEND itab.
itab-kunnr = '10000'.
itab-gsber = 'aa001'.
itab-value = 30.
itab-value2 = 90.
APPEND itab.
itab-kunnr = '10000'.
itab-gsber = 'bb001'.
itab-value = 10.
itab-value2 = 80.
APPEND itab.
itab-kunnr = '10000'.
itab-gsber = 'bb001'.
itab-value = 40.
itab-value2 = 30.
APPEND itab.
itab-kunnr = '10003'.
itab-gsber = 'aa001'.
itab-value = 30.
itab-value2 = 40.
APPEND itab.
itab-kunnr = '10005'.
itab-gsber = 'bb001'.
itab-value = 80.
itab-value2 = 60.
APPEND itab.
itab-kunnr = '10005'.
itab-gsber = 'cc001'.
itab-value = 20.
itab-value2 = 90.
APPEND itab.
itab-kunnr = '10005'.
itab-gsber = 'cc001'.
itab-value = 40.
itab-value2 = 20.
APPEND itab.
LOOP AT itab.
COLLECT itab INTO itab2.
ENDLOOP.
SORT itab2 BY kunnr value DESCENDING.
LOOP AT itab2.
WRITE 😕 itab2-kunnr, itab2-gsber, itab2-value2, itab2-value.
ENDLOOP.
11-13-2007 4:18 PM
hi mate,
that is completely wrong i think it gives me:-
10000 aa001 170 80
10000 bb001 110 50
10003 aa001 40 30
10005 bb001 60 80
10005 cc001 110 60
where i would expect
10000 aa001 80 90
10000 bb001 50 80
10003 aa001 30 40
10005 bb001 80 60
10005 cc001 60 90
I think one problem is value and value2 is the wrong way around.
But still with this correction the highest values are out. I think it is because you have collected and thats why it is displaying that value
I guess this isnt as basic as I intially thought
11-13-2007 4:27 PM
11-13-2007 4:35 PM
haha its okay mate - i thought initially it was a simple thing - but the more i think about it the more my brain is melting.......
hopefully someone will come along with an answer......
And I really do appreciate your help so far.
11-13-2007 4:47 PM
Hi Steve,
I modified the above program and I thik it will work for you :
REPORT zznsa.
DATA : BEGIN OF itab OCCURS 0,
kunnr TYPE char10,
gsber TYPE char10,
value(2) TYPE p DECIMALS 0,
value2(2) TYPE p DECIMALS 0,
END OF itab.
DATA : itab2 LIKE itab OCCURS 0 WITH HEADER LINE.
DATA idx LIKE sy-tabix.
itab-kunnr = '10000'.
itab-gsber = 'aa001'.
itab-value = 30.
itab-value2 = 50.
APPEND itab.
itab-kunnr = '10000'.
itab-gsber = 'aa001'.
itab-value = 20.
itab-value2 = 30.
APPEND itab.
itab-kunnr = '10000'.
itab-gsber = 'aa001'.
itab-value = 30.
itab-value2 = 90.
APPEND itab.
itab-kunnr = '10000'.
itab-gsber = 'bb001'.
itab-value = 10.
itab-value2 = 80.
APPEND itab.
itab-kunnr = '10000'.
itab-gsber = 'bb001'.
itab-value = 40.
itab-value2 = 30.
APPEND itab.
itab-kunnr = '10003'.
itab-gsber = 'aa001'.
itab-value = 30.
itab-value2 = 40.
APPEND itab.
itab-kunnr = '10005'.
itab-gsber = 'bb001'.
itab-value = 80.
itab-value2 = 60.
APPEND itab.
itab-kunnr = '10005'.
itab-gsber = 'cc001'.
itab-value = 20.
itab-value2 = 90.
APPEND itab.
itab-kunnr = '10005'.
itab-gsber = 'cc001'.
itab-value = 40.
itab-value2 = 20.
APPEND itab.
LOOP AT itab.
COLLECT itab INTO itab2.
ENDLOOP.
SORT itab BY kunnr ASCENDING gsber ASCENDING value2 DESCENDING.
LOOP AT itab2.
idx = sy-tabix.
READ TABLE itab WITH KEY kunnr = itab2-kunnr
gsber = itab2-gsber.
MOVE itab-value2 TO itab2-value2.
MODIFY itab2 INDEX idx.
ENDLOOP.
LOOP AT itab2.
WRITE : itab2-kunnr, itab2-gsber, itab2-value, itab2-value2.
new-line.
ENDLOOP.
Regards,
Nicolas.
11-13-2007 5:26 PM
sounds awkward but this is the only way as of now i guess .
After re-visiting your query i understood that you want sum up 3rd column and get highest from the fourth column.
So i think we can make two Int tables.
IT_0: col1, col2, col3, col4.
IT_1 : col1, col2, col3
IT_2: col1, col2, col4.
IT_3: col1, col2, col3, col4.
yes a definite loop on IT_0.
--while moving into IT_1 we can use collect.
--apply your logic to move only highest value of col4 into IT_2.
again loop at IT_1, read from it_2 with key col1 col2.
move corresponding to IT_4. and append.
This should certainly work . but as i said its a bit unorthodox way of processing.