Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
pdumas
New Contributor III

Join load and calculate fields with both tables

Hi,

After a join load, I need to calculate a field with the data from both tables

Example :

Table1 : Reference, Price

Table2 : Reference, Quantity

I want to build a new table with

Reference

Price from Table1

Quantity from Talbe2

Price*Quantity

Thanks

Pierre.

Tags (1)
2 Replies
prieper
Honored Contributor II

Join load and calculate fields with both tables

You will need to join both tables in order to calculate with the fields:

Table3: JOIN (Table1) LOAD * RESIDENT Table2;
LOAD *, Price*Quantity AS Cost RESIDENT Table3;
DROP TABLE Table3; DROP TABLE Table2; DROP TABLE Table1;


HTH
Peter

MVP
MVP

Join load and calculate fields with both tables

Joins are the way to go here. But technically, you don't need to do a join to use fields from two or more tables during a load. You can set up mapping tables:

[Price Map]:
MAPPING LOAD *
RESIDENT [Table1]
;
[Table3]:
LOAD
Reference
,Quantity
,applymap('Price Map',Reference) as "Unit Price"
,Quantity * applymap('Price Map',Reference) as "Total Price"
RESIDENT [Table 2]
;

One possible advantage is if you want a default value. When doing a join, mismatching rows will have null values. Using a map allows you to specify a default like 'Missing' if you wish to make it easier to search for these. You can always do that as a separate join step by joining the table back to itself, though.

Community Browser