7 Replies Latest reply: Nov 28, 2017 3:04 AM by A.M. van Keep RSS

    Total Cost by Order

    Neil Gabin

      Hi there

       

      I have a schema that looks like this where 1 Order Item has Many Menu Items

      2017-11-27 11.29.28 pm.png

      Order-Menu-Items

      1

      1

      1

      2

      2

      2

      3

      3

      3

       

      Order Menu Items     Cost

      1                                   10.00

      2                                   20.00

      3                                   30.00

       

      When I use =sum([Cost]) I get $60 which seems to come from Table 2

       

      I need to have the Cost pulled across to the Order Items table so I get $180 = (10x3) + (20*3) + (30*3) from table 1.

       

      This seems so simple. I must be missing something really basic.

       

      Neil

        • Re: Total Cost by Order
          A.M. van Keep

          Hi Neil,

           

          I should advice a script solution on this on.

          In the Script add a left join to the Order-Menu-Items.

           

          LEFT JOIN ('Order Items') LOAD

          Order-Menu-Items,

          Cost

          FROM Menu;

           

          If you can't edit the script then you could calculate the correct number with this expression (assuming that there is only 1 cost value per Order-Menu-Item). :

          COUNT(Order-Menu-Items) * Cost.

            • Re: Total Cost by Order
              Neil Gabin

              thanks A.M.

               

              The LEFT JOIN worked. Just have a few questions to better understand the solution if you don't mind.

               

              So I started with this...

              2017-11-28 07.25.51 am.png

              and after adding the LEFT JOIN I ended up with this...

              2017-11-28 07.24.50 am.png

              I do have a basic understanding of SQL joins and relation schemas, but can I ask in Qlik, are the Order Item and Menu Item tables physically combined and is the data from the Menu Item table duplicated or is this just a virtual representation of the data structure.

               

              Also, the relationship is one-to-many from left to right (as shown in the first image) and I was able to LEFT JOIN the first 2 tables as well and end up with this

              2017-11-28 07.42.57 am.png

              I tried to LEFT JOIN these 2 tables but got this error

              2017-11-28 07.47.41 am.png

               

              My question is do I need to LEFT JOIN all 4 tables given there is a one-to-many relationship across them all and one of the KPIs I'd like to calculated is Average Order Cost By Customer

               

              thanks so much for you help.

              Neil

                • Re: Total Cost by Order
                  A.M. van Keep

                  Hi Neil,

                   

                  So first of all answering your last question: you don't need to join all tables. When you join tables like the order menu item in your initial question it multiplies to rows from the [Menu Item] table times the rows of the [Order Item] table.

                   

                  Qlikview works basically like SQL. There are many examples of using joins found when using Google.

                  SQL JOINS

              • Re: Total Cost by Order
                Shraddha Gajare

                try

                 

                Sum(Cost) * Count([Order-Menu-Items])

                • Re: Total Cost by Order
                  Aron Williamson

                  Hi,

                   

                  I recreated it in Inline loads and provided the following solution:

                   

                  2017_11_27_16_00_39_baudercpsvr_Remote_Desktop_Connection.png

                  I basically used the script to create a new field called count then manipulated it on the front end with:

                   

                  Sum(CountOfDistinctItems*Cost)


                    • Re: Total Cost by Order
                      Neil Gabin

                      thanks Aron but what i didn't include was the fact that there is another table attached to the "Order Items" called Orders which has a date on it which would break your solution because the CountOfDistinctItems will vary over time for each Item.

                       

                      I appreciate your suggestion, so thank you.

                      Neil