11 Replies Latest reply: Oct 12, 2013 8:37 AM by Iskander Smagulov RSS

    Empty fields in table

    Iskander Smagulov

      Hello,

       

      I have a table called "Inventory cost" (pic 1)

      1.png

       

      Another table called "remained in stock" (pic 2)

      4.png

      So, I have to make final table

      3.png

      , where:

      quantity in the begining calculated like

      RangeSum(

        Sum({$< InOrOut = {0}, DateMovement = {'<=$(vMinSelectedDate)'}, Date >} Quantity),

        Sum({$< InOrOut = {1}, DateMovement = {'<=$(vMinSelectedDate)'}, Date >} Quantity*(-1))

      )

       

      Fields Quantity in, out and in the end calculated similarly.


      Field "avg cost":

      avg( aggr(sum(DISTINCT(cost +quantity)),Date, Item, DateItemCame))

       

      The problem is that some fields in column avg cost is missed. Everything else calculated excellent.

       

      Please help!

      I'm new in qlikview. And sorry for my english)

        • Re: Empty fields in table
          Michele Barini

          Hi,

          how the two tables are joined?

          I see both item and quantity in the two tables.

          I suppose you are working with the data of the secont table and using field from the first is not working fine.

          • Re: Empty fields in table
            Jonathan Dienst

            Hi

             

            I think your expression for average cost should look something like:

             

            avg(aggr(sum(Quantity / Cost), Item, DateItemCame))

             

            Regards

            Jonathan

              • Re: Empty fields in table
                Iskander Smagulov

                I've tried to use it. But the result was the same.

                I think the problem is with Date.

                 

                For example, if I not using Date in columns "Quantity on the beginning" and etc the Average column shows everything perfect. But not using Date, the quantity calculates incorrectly.

                  • Re: Empty fields in table
                    Jonathan Dienst

                    How would you like the movement date to relate to the cost date?

                      • Re: Empty fields in table
                        Iskander Smagulov

                        Thank you, Jonathan, for reply.

                        Actually they are not related. I don't want to relate them. Two tables joined only by "Item". What I want is to take 2nd table as base and take some columns from 1st to calculate the average cost

                          • Re: Empty fields in table
                            Jonathan Dienst

                            Well, if the quantity, cost and item all come the first table, and the item field has the same name as in the second table, and if it is only key between the two tables, then I see no reason that expression does not work. You might need to post a more detailed example, with the correct field names, so that I can better understand your data structure.

                             

                            Jonathan

                              • Re: Empty fields in table
                                Iskander Smagulov

                                Ok!

                                So,

                                table1 - "Inventory cost":

                                fields - Item, DateCome, QuantityCame, CostCame, Spending1, Spending2

                                 

                                table 2 - "Items movement":

                                fields - Item, DateMovement, InOrOut, QuantityMoved, Stock

                                 

                                Final table (what I want to see):

                                Stock, Item, AvgCost, QuantityForTheBeginning, QuantityIn, QuantityOut, QuantityInTheEnd.

                                 

                                Also, where the problem can sit in my opinion, the script

                                 

                                CalendarInventoryCameTemp:

                                LOAD

                                  Date

                                Resident Calendar;

                                 

                                Left Join (CalendarInventoryCameTemp)

                                LOAD

                                  Date as DateCome

                                Resident Calendar;


                                CalendarInventoryCame:

                                LOAD

                                  Date

                                  ,DateCame

                                Resident CalendarInventoryCameTemp;

                                DROP Table CalendarInventoryCameTemp;

                                 

                                The same script for DateMovement.

                        • Re: Empty fields in table
                          Iskander Smagulov

                          I made a mistake in posting, of course I use spending instead of quantity)..

                          So I calculate avg cost like:

                           

                          =avg( aggr(sum(DISTINCT(cost +spending)),Date, Item, DateItemCame)).. Works fine, but misses some Items. Why???.. I don't understand(

                            • Re: Empty fields in table
                              Jonathan Dienst

                              Iskander Smagulov wrote:

                              So I calculate avg cost like:

                               

                              =avg( aggr(sum(DISTINCT(cost +spending)),Date, Item, DateItemCame)).. Works fine, but misses some Items. Why???.. I don't understand(

                               

                              I dont understand what you are trying to calculate with that expression. Using distinct will only consider one cost+spending amount, even if there are several for different items. Perhaps you mean

                               

                                   avg(aggr(DISTINCT Sum(...

                               

                              Also, if either cost or spending is null or is missing, then cost+spending will evaluate to null. So use RangeSum(cost , spending) in place of cost+spending.

                               

                              I hope that helps

                              Jonathan

                                • Re: Empty fields in table
                                  Iskander Smagulov

                                  I tried to do as you said, Jonathan, thank you.

                                  But the result is the same. Some items is shown, and some is not. And I don't see the logic there. Because they all have the data in fields.

                                   

                                  My intuition tells me Date making some problems there.

                                  Please, any suggestions. 2nd day of a headache(