Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

want to show a field that is calculation of two fields from two different tables

I have two tables

Table 1

  • ProductID
  • Product Descrition
  • Sale Price

Table 2:

  • ProductID
  • Product Descrition
  • Purchase Price

I need a field in script that i can use in table box and other objects on sheets as

Profit = Sale price - Purchase price

any help in this would be great..

I am really new to QlickView, so clear and working code will really help alot

thanks

3 Replies
Not applicable
Author

Hi,

You can MAPPING and get Purchase Price to Table 1. You can read about MAPPING in help.
You can try the following code.

Table2_Map:
MAPPING Load ProductID,
[Purchase Price]
FROM Table2;

Table1:
LOAD *,[Sale Price]-[Purchase Price] as Profit; //This is Preceding Load
LOAD ProductID,
[Product Descrition],
applymap('Table2_Map',ProductID,0) as [Purchase Price],
[Sale Price]
FROM Table1;

Now you can use this profit field in the charts. Hope this will help.

SunilChauhan
Champion
Champion

Table 1:

Load

ProductID,

[Product Descrition],

ProductID&'-'&[Product Descrition] as Key,

[Sale Price]

fom path;

Table 2:

Load

ProductID&'-'&[Product Descrition] as Key,

ProductID as ProductID1 ,

[Product Descrition] as [Product Descrition1] ,

[Purchase Price]

from path;

reload the app and the take one pivot table

choose dimension and then in expression u write

[Sale Price]-[Purchase Price] or sum([Sale Price])-sum([Purchase Price])

hope this help

Sunil Chauhan
rohit214
Creator III
Creator III

hi

You can Use also join function

Table 1

  • ProductID
  • Product Descrition
  • Sale Price

Table 2:

  • ProductID
  • Product Descrition
  • Purchase Price

left join(Table 1)

load

ProductID,

Purchase Price

resident table2;

Table1_Final:

load

*,

Sale Price*Purchase Price as Profit

resident Table1;

Drop table Table1;

and one more thing if is there any syn key then remove syn key

May it helps you

thanks

rohit