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

Multiply two fields from different tables

Hello!! I need some help with multiplying data from two tables into one new field having one field in common between both tables.

I have Table1 with fields: [Machine] and [Speed] and Table2 with fields: [Machine] and [ProdHrs], I need to have a new field (in Table2) that has the product of [Speed] and [ProdHrs] for each record of [Machine].

Thanks!!

1 Solution

Accepted Solutions
Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

If you don't want to join 2 tables together, you can use mapping to grab one value out of another table:

SpeedMap:

mapping load

Machine,

Speed

resident Table1

;

Table3:

load

Machine,

ProdHrs,

ProdHrs * ApplyMap('SpeedMap', Machine, 0) as MyNewFieldd

resident Table2

;

Oleg

View solution in original post

23 Replies
Not applicable
Author

In your script:

SQL SELECT Table1.Machine, Speed, ProdHrs, Speed * ProdHrs As TotalTime

FROM Table1, Table2

WHERE Table1.Machine = Table2.Machine;

Not applicable
Author

You Could also try something like this although if all you need is to multiply NMiller's suggestion seems easiest:

TableTemp:

Load
Machine,
Speed;

SQL SELECT machine, speed
from table1;

LEFT JOIN (TableTemp)
SQL SELECT machine, ProdHours
from table2;


NewTable:

Load
machine,
speed,
ProdHours,
speed*ProdHours as NewField

Resident TableTemp;

Drop Table TableTemp;

Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

If you don't want to join 2 tables together, you can use mapping to grab one value out of another table:

SpeedMap:

mapping load

Machine,

Speed

resident Table1

;

Table3:

load

Machine,

ProdHrs,

ProdHrs * ApplyMap('SpeedMap', Machine, 0) as MyNewFieldd

resident Table2

;

Oleg

sathishkumar_go
Partner - Specialist
Partner - Specialist

Hi use

load

Machine,

Speed,

ProdHours,

(speed * ProdHours) as Mul

Resident Tablename;

drop table Tablename;

Not applicable
Author

Oleg,

This method of mapping is a great idea and I can see many uses for it but I am wondering how the ApplyMap() knows to use speed.

Are you only able to map one field at a time?

Marc

Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

Marc,

you specify what field to use in the mapping load. Yes, mapping load only allows 2 fields - the "from" and the "to". Indeed, mapping has many uses, and I found it much more friendly than a left join, especially if you only need a single field.

good luck!

Oleg

rbecher
MVP
MVP

Hi Oleg,

I use lookup( ) instead.

This is more flexible because you need no Map and you can retrieve different fields.

Ralf

Astrato.io Head of R&D
Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

Ralf,

I tried using lookup() long time ago, and it was causing some trouble, so I stayed away since then. Perhaps I should give it another try. Do you have any experience regarding performance - if it's applied to large data load, what's the "price" for convenience ?

Oleg

rbecher
MVP
MVP

Oleg,

lookup could be a bit slower but remember, it's all in memory! I wouldn't expect much difference.

Ralf

Astrato.io Head of R&D