calculation of openning qty from closing qty for next day?

[#1]

Hi All

I am having a table which has details of material, their closing quantity and date.

so daily this closing quantity is moving and I want to store their closing quantity as a opening quantity for next day.

Can anybody provide any logic/approach.

I have attached the sample data where input table is a having material, quantity and Date,

Pls check output table where I have to show opening quantity from previous closing quantity.

Thanks In advance

[tresesco]

Do you need them material wise?

[MayilVahanan]

Hi

Try like this

Test:

LOAD * INLINE [

    Date, Material, ClosingQTY

    8/10/2013, X1, 10

    8/10/2013, X2, 8

    8/10/2013, X3, 12

    9/10/2013, X1, 8

    9/10/2013, X2, 10

    9/10/2013, X3, 15

    10/10/2013, X3, 9

    10/10/2013, X1, 4

    10/10/2013, X2, 4

];

Load

  Date, Material, If(Material <> Previous(Material),' ', Previous(ClosingQTY)) As OpeningQty, ClosingQTY

Resident Test

Order By Material,Date;

DROP Table Test;

Hope tat helps

[#4]

Material and datewise

[tresesco]

Please see the attached sample.

[#6]

Thanks  Tresesco

[#7]

HI Tresesco

I have implemented it, and it is working fine but a problem arises when there is more material in previous day and less material on current day.

I have attached the sample QVW.

Pls check for closing QTY for date 09-10 which  is 47. This Closing QTY value suppose to be the opening QTY for next day but because of less nos of material on next day i.e.(date 10-10)  it rejects one record whose material is X4. This material is available on date 09-10 but not available on date 10-10-2013. I think this is the reason it is not calculating it.

Pls suggest how to calculate so that it gives correct opening QTY or any new logic?.