Sum value from different table using matching values

[tm_]

Hello Qlik community,

I have two different tables that look as below:

Table 1
BA	Tot_GW	Transport_Mode	Order Type	Country	Weekday
V	5000	Air	Day	SE	1
T	200	Road	Stock	SE	2
T	700	Road	Day	SE	1

Table 2
Route	BA	Transport_Mode	CC_CDC	Order Type	Weekday	Tot GW
Route1	V/T	Road	SE	Stock/Day/VOR	1,2,3,4,5	900

I wonder have I can aggregate the sum of the [Tot GW] column from table 1 to table 2 based on these matching fields between 1 and 2. For example I need to sum the value for both Brand = V or T, for Order Type = Stock or Day or VOR and for Weekday = 1 or 2 or 3 or 4 or 5.

If my routes in Table 2 would not have been aggregated on Weekday, Order Type and BA I would have been able to solve it with matching keys, however now I somehow need to sum values from Table 1 based on matching wildsearch into different fields in table2.

Any idea how I can solve this?

Much appreciated for the help in advance! 🙂

[dieterdec]

Hi Thomas,

you can try to get rid of the aggregation by doing a subfield load on table 2 to 'BA, 'order type' and 'weekday'

load

Route,

subfield(BA,'/'),

[transport mode],

[CC_CDC],

subfield([Order Type],'/'),

subfield(Weekday,','),

from Table 2;

this will multiply the rows of your second table but they will be unique - be careful with the repeated gross weight in that case!

Have not had such example myself, so am really curious if this will work out as planned?

good luck!

br// DD

[tm_]

Hello DD,

Thank you for your reply. If possible I would prefer not to populate new rows for the second table, unless I somehow can aggregate them back somehow?

BR TM

[dieterdec · Accepted solution]

Hi,

You need a unique identifier (key), this subfield load is a way to create the possible unique keys from the aggregated -table 1.   Such unique keys can be created in Table 2 as well - as each row is unique in table2 you don't need subfield load here.

A right join to table 1 from table to table 2 based on that key would give you the expected result and will keep the possibility to show the original amount of rows from both tables.

Please check attached if this is the outcome you're after.

BR//DD