requirement

[Former Member]

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.

[Former Member]

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.

ENDIF.

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

ENDIF.

ENDLOOP.

Regards,

Khushboo

[Former Member]

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.

[Former Member]

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

[Former Member]

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.