topic Re: requirement in Application Development and Automation Discussions

requirement

Former Member — Fri, 19 Apr 2013 15:39:38 GMT

Hi experts,

i have the following data in one table,

CID DATE REVENUE

================================

C100 20120101 100

C200 20120101 200

C300 20120101 300

C400 20120102 250

C500 20120102 300

C600 20120102 500

Now my requirement is that , i have to add 2 columns min_rev and max_rev and the output condition should be as shown below:

In the same date , we have to get the maximam revenue and minimum revenue and it has to be placed in the max_rev and min_rev columns as shown below :

CID DATE REVENUE MAX_REV MIN_REV

======================================================

C100 20120101 100 300 100

C200 20120101 200 300 100

C300 20120101 300 300 100

C400 20120102 250 500 250

C500 20120102 300 500 250

C600 20120102 500 500 250

I HOPE U GOT MY QUESTION:

PLS REPLY ASAP.

Re: requirement

Former Member — Fri, 19 Apr 2013 17:34:58 GMT

Hi!

Assuming that CID is the unique/ primary key field here:

select CID DATE REVENUE

INTO TABLE ITAB_1

FROM TABLE1 [JOIN TABLE2 ON CONDITION].

******ITAB_2 LIKE ITAB_1, WA_2 LIKE WA_1.

SORT ITAB_1 BY CID DATE.

LOOP AT ITAB_1 INTO WA_1.

AT NEW DATE.

WA_2-DATE = WA_1-DATE.

WA_2-CID = WA_1-CID.

WA_2-MAX_REV = WA1_REV.

WA_2-MIN_REV = WA1_REV.

APPEND WA_2 TO ITAB_2.

CLEAR WA_2.

END AT.

SORT ITAB_2 BY DATE.

READ TABLE ITAB_2 WITH KEY DATE = WA_1-DATE INTO WA_2.

IF SY-SUBRC = 0.

IF WA_2-CID <> WA_1-CID.

IF WA_2-MAX_REV <= WA_1-REV.

WA_2-MAX_REV = WA_1-REV.

ELSE

IF WA_2-MIN_REV >= WA_1-REV.

WA_2-MIN_REV = WA_1-REV.

ENDIF.

MODIFY ITAB_2 FROM WA_2 WHERE DATE = WA_2-DATE TRANSPORTING MIN_REV MAX_REV.

ENDIF.

ENDLOOP.

Regards,

Khushboo

Re: requirement

Former Member — Fri, 19 Apr 2013 18:16:13 GMT

Hi praveen ,

1) sort the table with date .

2) Use AT END in side the loop and get max , min vales( by comparing values) including date and CID into other internal table.

3) update max and min values to your table using date and CID.

Re: requirement

Former Member — Sat, 20 Apr 2013 04:48:23 GMT

Hi khusboo,

Pls can u explain a little bit more on this statement.

READ TABLE ITAB_2 WITH KEY DATE = WA_1-DATE INTO WA_2.

In the above statement what WA_1-DATE can hold ? I mean what data will it hold

Pls can u explain this one clearyly.

Hope u got my question.

tnx,

praveen.

Re: requirement

Former Member — Sat, 20 Apr 2013 07:21:30 GMT

Hi!

Instead of,

"READ TABLE ITAB_2 WITH KEY DATE = WA_1-DATE INTO WA_2. " you can also write:

LOOP AT ITAB_1 INTO WA_1.

AT NEW DATE.

WA_2-DATE = WA_1-DATE.

WA_2-CID = WA_1-CID.

WA_2-MAX_REV = WA1_REV.

WA_2-MIN_REV = WA1_REV.

APPEND WA_2 TO ITAB_2.

CLEAR WA_2.

END AT.

LOOP AT ITAB_2 INTO WA_2.

IF WA_2-DATE = WA_1_DATE AND WA_2-CID <> WA_1-CID.

IF WA_2-MAX_REV <= WA_1-REV.

WA_2-MAX_REV = WA_1-REV.

ELSE

IF WA_2-MIN_REV >= WA_1-REV.

WA_2-MIN_REV = WA_1-REV.

ENDIF.

MODIFY ITAB_2 FROM WA_2 WHERE DATE = WA_2-DATE TRANSPORTING MIN_REV MAX_REV.

ENDIF.

ENDLOOP.

=> But if you see, we are having to perform the same logic using a loop within a loop, which is not efficient. Rather than looping, we are first sorting ITAB_2 with date, and then with date as index we are directly searching for the record - binary search - which is faster than loop within loop.

Hope it helps. And let me know if you are getting the desired output.

If not i will check in the system and get back to you.

Regards,

Khushboo