Multiply two fields from different tables

[#1]

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!!

[#2]

In your script:

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

FROM Table1, Table2

WHERE Table1.Machine = Table2.Machine;

[#3]

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 · Accepted solution]

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]

Hi use

load

Machine,

Speed,

ProdHours,

(speed * ProdHours) as Mul

Resident Tablename;

drop table Tablename;

[#6]

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]

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]

Hi Oleg,

I use lookup( ) instead.

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

Ralf

[Oleg_Troyansky]

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]

Oleg,

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

Ralf