Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
IMPORTANT: Upcoming LEF Database Maintenance, Oct. 3rd - SEE DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

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
Highlighted
Partner
Partner

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
Highlighted
Partner
Partner

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

Highlighted
Partner
Partner

try

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

Highlighted
Creator
Creator

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)


Highlighted
Contributor III
Contributor III

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

Highlighted
Contributor III
Contributor III

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

Highlighted
Contributor III
Contributor III

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

Highlighted
Partner
Partner

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

Highlighted
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