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

[Oleg_Troyansky]

Ralf,

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 🙂

Oleg

[rbecher]

Good lookup!

😉

[Oleg_Troyansky]

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!

Oleg

[yblake]

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.

[rbecher]

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.

[rwunderlich]

I have avoided lookup because I found it to be extremly slow. Inspired by this post, I thought I would benchmark it again. I still found it very slow for large datasets. My tests were with 8.50.6206. The test was to multiply a quantity in the Orders table times the Price from the Items table. Orders was always 10 times the size of Items with an even distribution of Items.

I compared the ApplyMap() technique with the lookup(). Here's my results.

ItemCount | OrderCount | ApplyMap Seconds | Lookup() Seconds

5,000 | 50,000 | 0.59 | 2.47
50,000 | 500,000 | 6 | 179

Lookup() performance seemed to degrade with an increase in the looked-up table (Items). This seems to suggest that Lookup does not use an index to perform the search?

Attached is my test QVW if anyone wants to try to replicate (or improve) my results.

-Rob

[rwunderlich]

Oleg,

Great wiki entry. Thanks for posting it!

-Rob

[Oleg_Troyansky]

Thanks Rob, appreciate your feedback!

Since I'm too lazy to organize a blog like yours, I guess I'll specialize in writing Wikis.

If anyone has ideas about a good Wiki topic - shoot it to me, I'll try to write about it.

thanks!

Oleg

[#10]

Thanks to everybody!!

I spent two days reviewing all of your answers and some of them are too advanced for me, but the mapping method seems to work very Good!!

Thanks

HS

[bullish35]

I've tried using the ApplyMap recommendation that you posted above. I am clearly not translating your code correctly. I want to create two new fields (without creating a new table) to add to Table Two:

1) [Issue Age] x [Initial Face Amounts]

2) [Issue Age] x [Death Benefits]

Here is what I have:

Table_1:

LOAD

[Policy Number],

Mode,

[Death Benefit Option]

[Issue Age],

[Account Value],

[Loan Balance],

[Count]

FROM inv11006.qvd (qvd);

------------------------------------------------------------------------------------------------------------

Table_2:

LOAD

[Policy Number],

[Death Benefits],

[Initial Face Amounts],

[Coverage Count]

FROM inv21006.qvd (qvd);

AgeFaceBenefitsMap:

mapping load

[Policy Number],
[Issue Age]

resident Table_1;
load

[Initial Face Amounts],

[Death Benefits],

[Initial Face Amounts] * ApplyMap('AgeFaceBenefitsMap', [Issue Age], 0) as AgeFace,

[Death Benefits] * ApplyMap('AgeFaceBenefitsMap', [Issue Age], 0) as AgeDeathBen

resident Table_2;

Your response is greatly appreciated!

Thank you,

Ellen