Concatenating two tables

[ShellyG]

Hi everyone, 

I will need your help for the following:

I have already a main data table in my application. What I am trying to do is concatenate it with an excel table, which only has a couple of lines. 

The fields in the excel table are 'name of supplier', 'month', 'country' and 'amount'. All of these fields have exactly the same name in the excel table as the main table. 

What happens though is that for the countries that already exist in my main table, but also exist in the one I want to concatenate, I see two lines after the concatenation. For example, I will see Germany one time with one amount 51 (from the main table) and second time -21 (from the excel). What I would like to happen is that I would like to see Germany only once with 'Amount' (51 - 21) 30. 

It seems that it is not working because Qlik Sense doesn't calculate these two 'amount' columns in order to combine them. 

Any advise is appreciated. 

Best Regards,

Shelly 

[brunobertels]

Hi 

concatenate your two table as you do in a temp table 

(in qliksense concatenate will not result in summing and grouping by occurence you need to do it in a second hand)

Then reload this temp table :

load

'name of supplier',

'month',

'country'

Sum( 'amount') as amount

resident temptable

group by [country], [month], [name of supplier];

drop table temptable;