Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

noamhe
New Contributor

Calculate field with associate table

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?

Labels (3)
4 Replies
MVP
MVP

Re: Calculate field with associate table

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

Re: Calculate field with associate table

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 

 

 

Partner
Partner

Re: Calculate field with associate table

 

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

 

Partner
Partner

Re: Calculate field with associate table

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