Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Creator II
Creator II

Calculating daily stock levels

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

1 Solution

Accepted Solutions

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

1 Reply

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post