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

[#1]

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

[#2]

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]

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

[rohit214]

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