Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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 ...;
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
Might work but I have more then 2 columns in table 2