-
Re: Total Cost by Order
A.M. van Keep Nov 27, 2017 7:40 AM (in response to Neil Gabin)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 Nov 27, 2017 3:52 PM (in response to A.M. van Keep )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...
and after adding the LEFT JOIN I ended up with this...
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
I tried to LEFT JOIN these 2 tables but got this error
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 Nov 28, 2017 3:04 AM (in response to Neil Gabin)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.
-
-
-
Re: Total Cost by Order
Shraddha Gajare Nov 27, 2017 9:48 AM (in response to Neil Gabin)try
Sum(Cost) * Count([Order-Menu-Items])
-
Re: Total Cost by Order
Neil Gabin Nov 27, 2017 3:10 PM (in response to Shraddha Gajare )thanks for you reply, but that just gives me a total thats way, way too large
-
-
Re: Total Cost by Order
Aron Williamson Nov 27, 2017 11:08 AM (in response to Neil Gabin)Hi,
I recreated it in Inline loads and provided the following solution:
I basically used the script to create a new field called count then manipulated it on the front end with:
Sum(CountOfDistinctItems*Cost)
-
Test1.qvw 152.5 K
-
Re: Total Cost by Order
Neil Gabin Nov 27, 2017 3:54 PM (in response to Aron Williamson)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
-