Calculate field with associate table

[Anonymous]

Hey,

I have 2 tables (Sales and Product) who associated (by the ProductId field).

I'm trying to implement a new calculated field who take the sales price field from the Product table and multiply him with the sale rate field from the Sales table.

I know how to it in SQL but what is the right way to do it in Qlik sense?

[tresesco]

It's not much different from SQL. You can join the tables and multiply the fields to create the new field. Or, use applymap()

[phapalesaurabh]

Hi Noamhe ,

There are 2 ways to tackle this Problem,

1. Since there linkage between Product and Sales Table (Product Id) , Just by loading the Data and Creating calculated measure (Formula) will solve your req, just like sql. Instead of defining join , which we usually do in sql, Qlik identifies common fields between Tables and treat it as Linkage between Tables.

2. Other is creating new field in Sales table using applymap() function as suggested by Tresesco.

[kamalqlik]

Hi

It is possible from two way:

Method 1:

ProductSalesFinal:

load ProductId,

SalesPrice

from Product table;

innerJoin(Sales)

ProductId,

SalesRate

from Sales;

**** After this you can take the resident of above table and get the values: If you want aggregated results than use group by:

ProductSales:

load ProductId,

SalesPrice*SalesRate as SalesPriceRate,

SalesPrice,

SalesRate

resident ProductSalesFinal;

drop table ProductSalesFinal;

Method 2:

Use ApplyMap to get the results.

Regards

Kamal

[kamalqlik]

Hi Noamhe,



You can do like this:

Product:

Load Cost,

"Product Category",

"Product Code",

// Write all the field as per your table

From [lib://.....]

inner join(Product)

Sales:

load Customer_Num,

"Date",

// Write all the field as per your table

from [lib:// ....sales];

// Now take the resident for the output.

SaleProduct:

load *,

SalesPrice*Multiplier as SalesPriceRate

resident Product;

drop table Product;



// As i am not seeing Sales Rate field in your second table(Sales) column, i have used multiplier in that case, you can replace it with column you want to use to get results