Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
cancel
Showing results for 
Search instead for 
Did you mean: 
noamhe
Contributor
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
tresesco
MVP
MVP

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

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 

 

 

kamalqlik
Partner
Partner

 

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
Partner
Partner

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