Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Total Cost by Order

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

1 Solution

Accepted Solutions
avkeep01
Partner - Specialist
Partner - Specialist

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.

View solution in original post

8 Replies
avkeep01
Partner - Specialist
Partner - Specialist

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
Partner - Master III
Partner - Master III

try

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

Anonymous
Not applicable
Author

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)


Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

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
Not applicable
Author

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

avkeep01
Partner - Specialist
Partner - Specialist

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
Contributor
Contributor

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