Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

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

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

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

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

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

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

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