aggregate select on combination of several fields

[Former Member]

Hi All,

Today I got a requirement to display stock aging data for any previous period and I decided to use table CKMLHD&CKMLPP. But I then got a problem on selecting data from the large table CKMLPP. In this table we have some data like belows:

KALNR        BDATJ   POPER      LBKUM
100515932     2009    012          22.000
100515932     2010    001          12.000
100931044     2010    001          1.000
100931044     2010    002          1.000
...

Now we need to get stock data (field CKMLPP-LBKUM) from the latest period(CKMLPP-BDATJ + CKMLPP-POPER). For here we want to get the following records:

KALNR        BDATJ   POPER      LBKUM
100515932     2010    001          12.000
100931044     2010    002          1.000
...

Considering the table size (more than 4 million of lines), we don't want to select all of them into internal tables and then compare each other to get the latest data. I tends to use some aggregate expressions like max( CKMLPP-BDATJ + CKMLPP-POPER ) to minimize the workload, but unfortunately, the expression max() does not support column combination, it can only select max( CKMLPP-BDATJ ) or max( CKMLPP-POPER ), but that's meanless.

I know normally for material ledge records, it should have stock data in one line per period in CKMLPP even there is no GR/GI during the specified period, but for some reason, lots of period data not exist so we can not just use sql like select LBKUM from CKMLPP where BDATJ = p_period0(4) and POPER = p_period4(3).

So could any of you give me an idea how to handle this situation efficiently?

Thank you very much!

Best Regards,

Jeff

[former_member907073]

Hi

Instead of doing it in one select ..Make use of select ..endselect ( with package size). After u have ur data in final table.

Sort it as per criteria u want & then Use collect. Tereafter u can rearrange the date as u want.

Regards,

AS

[deepak_dhamat]

Hi ,

Better go with select statement

select KALNR

BDATJ

POPER

UNTPER

LBKUM

into corresponding fields of table it_CKMLPP

where UR condition

sort it_CKMLPP by kalnr ,bdatj ,poper by desc .

delete it_CKMLPP comparing kalnr bdatj ,proper .

You will get only Latest DAta .

This would be easy .

Try to minimise your Data using Where condition with proper Index field ,also select all primary key fields in selection .

regards

Deepak.

[Former Member]

Hi Deepak,

Thank you for the solution, because i don't know which materials lose their period ledge data, so i have no idea what condition i can use to minimize the workload, that means i probably have to select all the 4 million records from CMKLPP and then sort them using your method to get the latest period, i can't imagine what will happen, but i know it's really a hard work. User want to show the data in less than 2 minutes. If i could use max( bdatj + poper ), life would be easier.

I'm now trying to analyze the reason of missing period data because user told me since they changed the price control indicator from standard price to moving average price for some kinds of materials in a specific period, and then the material ledge data stopped creating periodically unless the cost changed in the plant, if it's true, then maybe we can concentrate on that period.

Technically speaking, at present i have no alternative way beside yours, what i can optimize is the condition on table CKMLPP, to make the data selected to be less. However, I still expect more exciting suggestions eagerly

Thanks all the guys for noticing my issue.

Jeff

[deepak_dhamat]

Hi ,

because i don't know which materials lose their period ledge data, so i have no idea what condition i can use to minimize the workload

it will be better to ask Your Function team member to check data in table and give requirment accordingly .

also give date range as selection parameter and pass it to Table Where condition : Use Select-options .

regards

Deepak.

[Former Member]

Just checked with the funcational team and found that for materials with moving average price, if there is no cost changed during a specific period, then no record will be created, however, if something happen like GI/GR, then SAP will fill the missing period data, so I think we can compare current material stock with the data in CKMLPP, if there has stock now but cannot find specific period stock from CKMLPP record with excluding the materials created after the period, the left should be the missing parts

I've written code based on the logic and user checked with no error found at present. Seems it's ok. But I still expect SAP can enable combination of table fields as query conditions.

Thank Deepak for the suggestion.

Jeff

[former_member1125862]

HI,

Try using of ranges....

Kruthik