Opening and Closing Stock Values Logic

[#1]

Hi All,

           What are the best and easy possibilites are there to get the output as mentioned in the excel with input. Every one logics are welcome.

Regards,

Ravi.

[#2]

You are asking how to calculate Actual Opening and Actual Closing ?

[#3]

Yes Absolutely, using script in Edit script enviromment.

[#4 · Accepted solution]

//check this:

robo:

LOAD * INLINE [

    Dealer, KEY, Date, WholeSale, Retail, Opening, Closing

    D1, D101-01-2013, 01-01-2013, 50, 2, 0, 48

    D1, D103-01-2013, 03-01-2013, 10, 5, 48, 53

    D1, D105-01-2013, 05-01-2013, 103, 8, 53, 148

    D1, D106-01-2013, 06-01-2013, 84, 1, 148, 231

    D1, D110-01-2013, 10-01-2013, 7, 2, 231, 236

    D1, D111-01-2013, 11-01-2013, 47, 6, 236, 277

    D1, D112-01-2013, 12-01-2013, 90, 2, 277, 365

    D1, D113-01-2013, 13-01-2013, 36, 3, 365, 398

    D1, D114-01-2013, 14-01-2013, 2, 3, 398, 397

    D1, D117-01-2013, 17-01-2013, 25, 7, 397, 415

    D1, D118-01-2013, 18-01-2013, 10, 0, 415, 425

    D1, D120-01-2013, 20-01-2013, 30, 0, 425, 455

    D1, D121-01-2013, 21-01-2013, 5, 9, 455, 451

    D1, D123-01-2013, 23-01-2013, 5, 3, 451, 453

    D1, D124-01-2013, 24-01-2013, 20, 6, 453, 467

    D1, D125-01-2013, 25-01-2013, 2, 10, 467, 459

    D1, D127-01-2013, 27-01-2013, 10, 0, 459, 469

    D1, D128-01-2013, 28-01-2013, 12, 15, 469, 466

    D1, D130-01-2013, 30-01-2013, 25, 2, 466, 489

    D1, D102-02-2013, 02-02-2013, 10, 0, 489, 499

    D1, D104-02-2013, 04-02-2013, 5, 7, 499, 497

    D1, D106-02-2013, 06-02-2013, 4, 0, 497, 501

    D1, D108-02-2013, 08-02-2013, 15, 3, 501, 513

];

final:

NoConcatenate LOAD Dealer,

     KEY,

     Date,

     WholeSale,

     Retail,

     num(WholeSale-Retail) as roboCalc,

     if(rowno()=1, num(WholeSale-Retail), peek('Closing')+num(WholeSale-Retail)) as Closing,

     if(rowno()=1,0, if(rowno()=2,peek('roboCalc')+num(WholeSale-Retail) , peek('Opening')+num(WholeSale-Retail))) as Opening

resident robo;

drop field roboCalc;

drop table robo;

[#5]

Super Robo:)