sum two columns from different tables in script

[denis115]

Hi there!

I ve got promblem! 

I need to calculate sum of two columns from two different tables.

table 1

id, quntity, base_price

table 2 

id, shop_price 

In script I want to select smth like this :

id, quantity*base_price , quantity* table2.shop_price 

how can I get this? Thank you!

[denis115]

Hi there!

I ve got promblem! 

I need to calculate sum of two columns from two different tables.

table 1

id, quntity, base_price

table 2 

id, shop_price 

In script I want to select smth like this :

id, quantity*base_price , quantity* table2.shop_price 

how can I get this? Thank you!

[stevejoyce]

Join the 2 tables together then you can do this.

(you may need to aggregate table 2 first if needed)

The "ideal" way is apply map to do this.

map_table2:

mapping load 

id,

sum(shop_price)

from ...

group by id;

table 1:

load

id,

quantity*base_price ,

quantity* applymap('map_table2', id) as Quantity_X_ShopPrice

from ...;

[rwunderlich]

You can use a mapping table and applymap() like this:

Price_Map:
Mapping LOAD id, shop_price
Resident table2;

Table1:
LOAD
  id, quantity, base_price,
  quantity * ApplyMap('PriceMap', id) as shop_total
FROM ...;

More on creating table associations in script here:
https://qlikviewcookbook.com/2020/03/creating-temporary-script-associations/

-Rob

[denis115]

Might work but I have more then 2 columns in table 2