Hi there
I have a schema that looks like this where 1 Order Item has Many Menu Items
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
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.
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
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.
thanks for you reply, but that just gives me a total thats way, way too large
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)
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
