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!!
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
ProdHrs,
ProdHrs * ApplyMap('SpeedMap', Machine, 0) as MyNewFieldd
resident Table2
Oleg
Oleg Troyansky
Natural Synergies, Inc.
In your script:
SQL SELECT Table1.Machine, Speed, ProdHrs, Speed * ProdHrs As TotalTime
FROM Table1, Table2
WHERE Table1.Machine = Table2.Machine;
You Could also try something like this although if all you need is to multiply NMiller's suggestion seems easiest:
TableTemp:LoadMachine,Speed;SQL SELECT machine, speed from table1;LEFT JOIN (TableTemp)SQL SELECT machine, ProdHoursfrom table2;NewTable:Loadmachine,speed,ProdHours,speed*ProdHours as NewFieldResident TableTemp;Drop Table TableTemp;
Hi use
Speed,
ProdHours,
(speed * ProdHours) as Mul
Resident Tablename;
drop table Tablename;
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
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!
Hi Oleg,
I use lookup( ) instead.
This is more flexible because you need no Map and you can retrieve different fields.
Ralf
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 ?
lookup could be a bit slower but remember, it's all in memory! I wouldn't expect much difference.
I got curious and tested it. You are right, both functions took about the same time, only lookup caused a slightly higher (approx. +20%) memory consumption.
Perhaps I should give lookup() another chance :-)
Good lookup!
;-)
Folks,
considering the interest this post had generated, and inspired by Rob's blog entry, I decided to write a Wiki page based on this material. Feel free to check it out!
I compared retrieving lookup fields from 2 tables, into a big one (1 500 000 records).
In that case,"lookup" is 4 times slower (2 mn) than a classic "left join" (30 sec.).
Certainly a "join" retrieve similar field values once, which is faster, whereas lookup search is performed on each record. i.e. if you have 100 unique machines IDs in a 1000000 records table, join will search and retrieve 100 Machine speeds records, lookup will repeat search and retrieve 1000000 times.
Join should be always faster. Best case would be a join and calculation on database level in the SQL query itself. But, if you have several data sources you would need an alternative.
Join in QV and calculation in a second load (see above) could lead into a out of memory problem because data are duplicated.
Community
QlikView Home Page
Call +1 (888) 828-9768 (N. America) or click on a link below: