Previous Record Using Select statement.

[adeel_sarwar]

Hi,

Is it possible using Select statement to retrieve the previous Record. For Example:-

I have two Tables Header and Item Tables

In the Header table "AAAA" I have 1 Record

MATNR

MaterialA

In the Item table "BBBB I have 2 Records

MATNR PRICE

MaterialA 20

MaterialA 30

Now using the select statement I want 1 output. The output should be as below just on 1 line

MATNR Current Price Previous Price

MaterialA 30 20

Hope that makes sense. I am ok with getting the current price as this is easy enough using the select * statement and it will bring the last record.

The Question is how can i obtain the Previous Price using the above as an Example?

Thanks

Adeel

[Former Member]

Hi,

no that won't work with one select statement.

You have to loop over both entries of the second table and fill in internal table in the form you want.

I hope that helps?

Bye

Frank

[Former Member]

Since you already have both records in the internal table, I don't think there's any reason to go back to the database.

Rob

[adeel_sarwar]

Hi,

Can some one give me an example code on how i can do this using loop?

thanks

[tarangini_katta]

Hi,

loop at it_item into wa_item.

read table it_header into wa_hedaer with key matnr = wa_item-matnr.

Populate the final internal table which you want to display.

endloop.

Thanks

[adeel_sarwar]

Hi,

I am ok with loops however i don't know how i could use the loop to obtain the information in the above example the -1 record.

That is the code i require

Thanks

[tarangini_katta]

Hi,

This may work for you.

you can do this thing in following way..

WORK_AREA AND WORK_AREA1 should have line type of t_table

data: w_num type i.

loop at T_TABLE INTO WORK_AREA.

if sy-tabix > 1.

w_num = sy-tabix.

w_num = w_num - 1 .

now read the table T_TABLE INTO WORKAREA2 WITH INDEX W_NUM.

endloop.

this code is worked for me.

This code answered to my question .

Thanks

[adeel_sarwar]

Hi,

Thanks for your reply, still having issues, might be easy if i paste the code. I have not added the "New Loop Code".

What I am trying to do is put all the data required in itab from table a005 and table konp.

Can you please help me add the correct code below. I am trying to get the previous condition price which is konp-kbetr.

Thanks

Adeel

DATA: l_count TYPE I.

DATA: BEGIN OF itab OCCURS 0,

amsg(100) TYPE C,

kunnr LIKE a005-kunnr,

matnr LIKE a005-matnr,

kbetr LIKE konp-kbetr.

DATA: END OF itab.

CLEAR:conc,

chnge.

SELECT kunnr matnr FROM a005 INTO TABLE itab

WHERE vkorg = a005-vkorg

AND matnr = a005-matnr

AND kunnr = a005-kunnr.

CONCATENATE a005-kunnr a005-matnr INTO itab-amsg.

MOVE itab-amsg TO conc.

SELECT kbetr FROM konp INTO TABLE itab

WHERE kschl = a005-kschl

AND knumh = a005-knumh.

SORT itab BY amsg.

LOOP AT itab.

ADD 1 to l_count.

AT END OF amsg.

MOVE l_count TO chnge.

CLEAR l_count.

ENDAT.

ENDLOOP.

IF as = 'X'.

CHECK chnge NE '1'.

ENDIF.

[adeel_sarwar]

Small issue

[adeel_sarwar]

Hi,

Thanks for all your help. It is working now however i have a small issue. Ok the program is date restricted. So if i run it without a date restriction it brings for example the following values :-

Material Price PreviousPRICE Date

MaterialA £1 £0 01/01/2009

MaterialA £5 £1 02/02/2009

MaterialA £10 £5 03/03/2009

When i run the report for 03/03/2009 it brings the following

Material Price PreviousPRICE Date

MaterialA £10 £0 03/03/2009

I expected the Previous Price to be £5

Thanks in advance.

Adeel

Loop Code

data: w_num type i.

LOOP AT itab.

IF sy-tabix > 1.

w_num = sy-tabix.

w_num = w_num - 1.

MOVE konp-kbetr TO old_price.

ENDIF.

ENDLOOP.

pprice = old_price.

[tarangini_katta]

Hi Adeel,

IF u get the data based on the particular date u r sying it is bringing only one record to u r intrenal table.

if that is the case do one thing then i think sy-tabix will not be grater than 1.

so the value will not be moved.

if u want to get the old value in date condition laso.

LOOP AT itab.

IF sy-tabix > 1.

w_num = sy-tabix.

w_num = w_num - 1.

MOVE konp-kbetr TO old_price.

V_temp_price = old_price.

EXPORT old_price from konp-kbetrTO MEMORY ID 'MID'.

ENDIF.

impoRT old_price to V_TEMP_PRICE FROM MEMORY ID 'MID'.

ENDLOOP

pprice = V_TEMP_PRICE.

Hope this may help you.

Thanks

[adeel_sarwar]

Hi Tarangini

Tried that same result. I copied your code word to word.

Any other ideas?

thanks

[tarangini_katta]

Hi adeel,

I think memory id is refrehing after every exceution.

Try to write Export statement above if condition.

First run with out date condition and see the MID in debug.

Then run with date condition and MID in debug if it refreshes it wont for you.

IF it is same value then it will work for you.

check this once.

IF you dont get it please let me know.

Thanks

[adeel_sarwar]

Hi,

Tried it still not working. I am right in saying i don't have to define 'MID'.

Code I am using.

DATA: w_num TYPE I,

old_price LIKE konp-kbetr,

v_temp_price LIKE konp-kbetr.

LOOP AT itab.

EXPORT old_price FROM konp-kbetr TO MEMORY ID 'MID'.

IF sy-tabix > 1.

w_num = sy-tabix.

w_num = w_num - 1.

MOVE konp-kbetr TO old_price.

v_temp_price = old_price.

ENDIF.

IMPORT old_price TO v_temp_price FROM MEMORY ID 'MID'.

ENDLOOP.

pprice = v_temp_price.

[tarangini_katta]

Hi Adeel,

With out defining MID.The export parameter does not hold the value.

I think u need to declare the MID.

Thanks

[dev_parbutteea]

Hi,

please use the following logic:

DATA: w_num TYPE I,

old_price LIKE konp-kbetr,

v_temp_price LIKE konp-kbetr.

field-SYMBOLS: <fs_tab> TYPE type_of_ur_table.

data: wa_previous type type_of_ur_table.

----> sort itab by matnr date.

LOOP AT itab assigning <fs_tab>.

IF sy-tabix > 1.

w_num = sy-tabix.

w_num = w_num - 1.

read table itab into wa_previous " read previous record

index w_num.

if sy-subrc = 0.

if wa_previous-matnr = <fs_tab>-matnr. " if previous record matnr are equal

<fs_tab>-old_price = wa_previous -new_price.

endif.

endif.

ENDLOOP.

Edited by: Dev Parbutteea on Mar 2, 2009 10:48 AM

[Former Member]

hi,

try this

types: begin of it_final ,

matnr

item1

item2

end of it_final.

select * from into itab1.

itab2[] = itab1[].

*both have same structure

loop at it_tab1 into w_tab1.

w_final-matnr = w_tab1-matnr.

w_final-item1 = w_tab1-item1

read read table it_tab2 into w_tab2 index 2 with key matnr eq it_tab1-matnr.

if sy-subrc eq 0.

w_final-item2 = w_tab1-item2

endif.

append w_fianl to it_final.

endloop.

[santosh_kumarm]

Hi Adeel Sarwar ,

Create a internal table with 3 columns.

below are the data for 2 internal tables

Internal table 1

MATNR

MaterialA

internal tabl 2

MATNR PRICE

MaterialA 20

MaterialA 30

internal table 3

MATNR , a 20.

loop internal table 1

internal table- fields = internal table1-MATNR.

loop internal table 2 where field = internal table1-field.

internal table-field = A

internal table-field = 30.

endlop.

append internal table 3.

endloop

by this way you can have the record in a single line in internal table 3.

Hope it shall be useful

Regard

Santosh

[Former Member]

Hi,

If you have already records in the internal table then why you want fetch data from DB. You can directly print that as per your requirement. Still you have any quiries please let me know.

Regards

Md.MahaboobKhan

[adeel_sarwar]

Hi,

Thankyou everyone for all your help. Still not working. I am pasting the full code you might spot something. Like mentioned before it works perfect if i don't restrict the dates. it only fails on restriction of dates. If some one can contact me via contact details, i could possibly do a webex where you could have a look.

Thanks

Adeel

TABLES rv12l.

SELECTION-SCREEN BEGIN OF BLOCK Date

WITH FRAME TITLE text-100.

SELECT-OPTIONS Date FOR RV12L-DATINT

DEFAULT SY-DATLO TO '99991231'

NO-EXTENSION

MODIF ID IA.

DATA DATLOW LIKE A005-DATAB.

DATA DATHIGH LIKE A005-DATBI.

DATLOW = DATE-LOW.

DATHIGH = DATE-HIGH.

IF DATLOW IS INITIAL.

DATLOW = '00010101'.

IF DATHIGH IS INITIAL.

DATHIGH = '99991231'.

ENDIF.

ENDIF.

SELECTION-SCREEN: END OF BLOCK Date.

CHECK ( ( A005-DATAB >= DATLOW AND A005-DATBI <= DATHIGH ) OR

( A005-DATAB >= DATLOW AND A005-DATBI >= DATLOW ) ).

DATA: l_count TYPE I,

w_num TYPE I,

zkbetr LIKE konp-kbetr,

old_price LIKE konp-kbetr,

mid(60) type c,

v_temp_price LIKE konp-kbetr.

DATA: BEGIN OF itab OCCURS 0,

amsg(100) TYPE C,

kunnr LIKE a005-kunnr,

matnr LIKE a005-matnr,

knumh LIKE a005-knumh,

kbetr LIKE konp-kbetr.

DATA: END OF itab.

CLEAR:conc,

chnge,

pprice,

v_temp_price,

old_price.

SELECT kunnr matnr knumh FROM a005 INTO TABLE itab

WHERE vkorg = a005-vkorg

AND matnr = a005-matnr

AND kunnr = a005-kunnr.

CONCATENATE a005-kunnr a005-matnr INTO itab-amsg.

MOVE itab-amsg TO conc.

SELECT kbetr FROM konp INTO zkbetr

WHERE knumh = a005-knumh

AND kopos = '1'.

ENDSELECT.

MOVE zkbetr TO itab-kbetr.

SORT itab BY amsg.

LOOP AT itab.

ADD 1 to l_count.

AT END OF amsg.

MOVE l_count TO chnge.

CLEAR l_count.

ENDAT.

ENDLOOP.

LOOP AT itab.

EXPORT old_price FROM konp-kbetr TO MEMORY ID MID.

IF sy-tabix > 1.

w_num = sy-tabix.

w_num = w_num - 1.

MOVE konp-kbetr TO old_price.

v_temp_price = old_price.

ENDIF.

IMPORT old_price TO v_temp_price FROM MEMORY ID MID.

ENDLOOP.

pprice = v_temp_price.

IF as = 'X'.

CHECK chnge NE '1'.

ENDIF.

[dev_parbutteea]

> SELECT kunnr matnr knumh FROM a005 INTO TABLE itab

> WHERE vkorg = a005-vkorg

> AND matnr = a005-matnr

> AND kunnr = a005-kunnr.

I dont understand where you are getting the A005 values

>

> CONCATENATE a005-kunnr a005-matnr INTO itab-amsg.

> MOVE itab-amsg TO conc.

what r u doing with the conc value?

> LOOP AT itab.

> EXPORT old_price FROM konp-kbetr TO MEMORY ID MID.

> IF sy-tabix > 1.

> w_num = sy-tabix.

> w_num = w_num - 1.

> MOVE konp-kbetr TO old_price.

> v_temp_price = old_price.

> ENDIF.

> IMPORT old_price TO v_temp_price FROM MEMORY ID MID.

> ENDLOOP.

where are you populating the konp-kbetr value ?

Regards.

[adeel_sarwar]

Hi Dev,

This is made via a query SQ02 as it is easier. I am using the ABAP Code for additional functionality. This report is basically showing customer pricing between a given date and the previous price of the condition. A005 contains the table with Material, Start and End of condition etc. KONP contains the Price of the condition.

Please ignore the CONC value as the purpose of CONC value was a user request for something completley different.

Regards

Adeel

[adeel_sarwar]

?

[adeel_sarwar]

Thanks for all your help. It works now. Tranginis solution works. Thanks again all.