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
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.
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.
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.
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:
Test1.qvw 152.5 K