Calculating daily stock levels

[valpassos]

Hi guys!

I am having trouble understanding stock levels.

In calculating the daily position of stock levels, I have this ordering logic in the script:

tmp_Stock:

LOAD

    IDCompany,

    Date,

    Product,

    Warehouse,

    "Stock movements",

   "# Final Stock"

Resident another_tmp_Stocks

ORDER BY IDCompany,Product,Warehouse,Date desc;

Stock:

IF(IDCompany=Previous(IDCompany),

    IF(Product=Previous(Product),

        IF(Warehouse=Previous(Warehouse),

           Peek("# Final Stock")-Peek("Stock movements"),

                "# Final Stock"),

             "# Final Stock"),

         "# Final Stock") AS "# Final Stock",

    "Stock movements",

    IDCompany,

IF(IDCompany=Previous(IDCompany),

    IF(Product=Previous(Product),

        IF(Warehouse=Previous(Warehouse),

            Peek("# Final Stock")-Peek("Stock movements"),

                "# Final Stock"),

            "# Final Stock"),

         "# Final Stock")  - "Stock movements" AS "# Initial Stock"

resident tmp_Stock; 

Can someone please explain to me, with examples, what exactly is this logic doing? My direct struggles are:

1. What does the ORDER BY ensures in the tmp_Stock table that will be useful for the Stock table?

2. What is the purpose of all the ordered IFs? Are we looking at the consecutive previous records in the extraction process order or in front-end straigh table order?

3. Why does it seem that the logic to come up with the Final and Initial stock are the same? (the same peek & previous combination)?

Many thanks in advance!

Lisa

[jonathandienst · Accepted solution]

1. The ORDER By gets the table in the correct order for comparing consecutive values in the Stock table. It could be moved to the Stock table load as an alternative.

2. The Ifs are comparing the consecutive values to be sure they refer to the same company, product and warehouse.

3. They are not the same - initial stock is the final stock less the movement