Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
tm_
New Contributor II

Sum value from different table using matching values

Hello Qlik community,


I have two different tables that look as below:

Table 1     
BATot_GWTransport_ModeOrder TypeCountryWeekday
V5000AirDaySE1
T200RoadStockSE2
T700RoadDaySE1

 

Table 2      
RouteBATransport_ModeCC_CDCOrder TypeWeekdayTot GW
Route1V/TRoadSEStock/Day/VOR1,2,3,4,5900

 

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 or or 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! 🙂

1 Solution

Accepted Solutions
dieterdec
New Contributor II

Re: Sum value from different table using matching values

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

 

 

 

3 Replies
dieterdec
New Contributor II

Re: Sum value from different table using matching values

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_
New Contributor II

Re: Sum value from different table using matching values

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

Re: Sum value from different table using matching values

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