topic Re: multiply 2 fields from different tables in QlikView

multiply 2 fields from different tables

ELI4 — Mon, 30 Aug 2021 16:58:06 GMT

Hi there. How can I multiply 2 fields from different tables? Any Suggestions?

Thanks!

Re: multiply 2 fields from different tables

stevejoyce — Mon, 30 Aug 2021 17:28:37 GMT

Either join the fields from different tables into same table and then do multiplication, or create a mapping table for one of the 2 tables and use applymap.

Example:

table1:
load * inline [
dim1, value1
a, 10
b, 20
c, 30

];

table2:
mapping load * inline [
dim2, value2
a, 50
b, 60
c, 70
]
;

table3:
load
dim1 as dim3
,value1 * applymap('table2', dim1) as value3

resident table1;

Re: multiply 2 fields from different tables

ELI4 — Mon, 30 Aug 2021 17:57:16 GMT

Hi steve. thanks for answering. I used join to join the tables but when I want to do the multiplication, it does not recognize the field that I joined. why ?

Re: multiply 2 fields from different tables

stevejoyce — Mon, 30 Aug 2021 17:58:29 GMT

can you share your script code? are you joining before you do multiplication, and not in the same statement?

Re: multiply 2 fields from different tables

ELI4 — Mon, 30 Aug 2021 18:07:54 GMT

Salesnews: Load *, (Sales-CostPerUnit) as Benefit; LOAD ItemID, Date, CampaignID, DocumentNumber, StoreHanut, "Time", SalesmanID, if( Quantity>= 0, 'Sold', 'Return' ) as Status, ApplyMap('CampaingID_Map', CampaignID , 'No Campaign' ) as NameofCampaign, TotalPriceBeforVat, Quantity, (TotalPriceBeforVat*Quantity*1.17) as Sales FROM [lib://ExtractDB/Salesnews.qvd] (qvd); left Join Stock: LOAD ItemID, Cost as CostPerUnit FROM [lib://ExtractDB/Stock.qvd] (qvd);

Re: multiply 2 fields from different tables

stevejoyce — Mon, 30 Aug 2021 18:23:42 GMT

They only multiplication i see you doing is all from Salesnews.qvd so i'm not sure where your issue is. Add Exit script; or step through with the debugger after your join and before your multiplication. Make sure you have the right field name.

Re: multiply 2 fields from different tables

ELI4 — Mon, 30 Aug 2021 19:34:50 GMT

When i want to do

(Sales-CostPerUnit) as Benefit;

error appears:

Field 'CostPerUnit' not found

Re: multiply 2 fields from different tables

stevejoyce — Mon, 30 Aug 2021 20:04:19 GMT

Yes, i believe you had another post open with same issue. You are joining CostPerUnit AFTER you do this calculation. So at the time you do Sales-CostPerUnit, this field doesn't exist in that table yet. I assume there is 1 CostPerUnit for each ItemID, so we can do mapping load for this...

map_ItemID_CostPerUnit:
mapping LOAD
ItemID,
Cost as CostPerUnit
FROM [lib://ExtractDB/Stock.qvd]
(qvd);

Salesnews:
Load
*,
(Sales-applymap('map_ItemID_CostPerUnit', ItemID)) as Benefit;
LOAD
ItemID,
Date,
CampaignID,
DocumentNumber,
StoreHanut,
"Time",
SalesmanID,
if( Quantity>= 0, 'Sold', 'Return' ) as Status,
ApplyMap('CampaingID_Map', CampaignID , 'No Campaign' ) as NameofCampaign,
TotalPriceBeforVat,
Quantity,
(TotalPriceBeforVat*Quantity*1.17) as Sales
FROM [lib://ExtractDB/Salesnews.qvd] (qvd);

Re: multiply 2 fields from different tables

ELI4 — Mon, 30 Aug 2021 20:32:52 GMT

8 hours with this. You're a genius!!!
THANK YOU THANK YOU THANK YOU

Re: multiply 2 fields from different tables

stevejoyce — Mon, 30 Aug 2021 20:46:33 GMT

No problem, glad you have it resolved! Please mark as solution so thread can be marked appropriately.