Total Cost by Order

[Anonymous]

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

[avkeep01 · Accepted solution]

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.

[shraddha_g]

try

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

[Anonymous]

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)

[Anonymous]

thanks for you reply, but that just gives me a total thats way, way too large

[Anonymous]

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

[Anonymous]

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

[avkeep01]

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

[aditya225]

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