topic Multiply two fields from different tables in QlikView

Multiply two fields from different tables

Thu, 23 Apr 2009 02:51:48 GMT

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

Multiply two fields from different tables

Thu, 23 Apr 2009 03:10:46 GMT

In your script:

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

FROM Table1, Table2

WHERE Table1.Machine = Table2.Machine;

Multiply two fields from different tables

Thu, 23 Apr 2009 03:31:28 GMT

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;

Multiply two fields from different tables

Oleg_Troyansky — Thu, 23 Apr 2009 03:37:19 GMT

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

Multiply two fields from different tables

sathishkumar_go — Thu, 23 Apr 2009 04:05:51 GMT

Hi use

load

Machine,

Speed,

ProdHours,

(speed * ProdHours) as Mul

Resident Tablename;

drop table Tablename;

Multiply two fields from different tables

Thu, 23 Apr 2009 04:37:02 GMT

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

Multiply two fields from different tables

Oleg_Troyansky — Thu, 23 Apr 2009 04:56:35 GMT

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

Multiply two fields from different tables

rbecher — Thu, 23 Apr 2009 17:43:13 GMT

Hi Oleg,

I use lookup( ) instead.

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

Ralf

Multiply two fields from different tables

Oleg_Troyansky — Thu, 23 Apr 2009 19:17:56 GMT

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

Multiply two fields from different tables

rbecher — Thu, 23 Apr 2009 20:32:02 GMT

Oleg,

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

Ralf

Multiply two fields from different tables

Oleg_Troyansky — Thu, 23 Apr 2009 21:58:44 GMT

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

Multiply two fields from different tables

rbecher — Thu, 23 Apr 2009 22:07:27 GMT

Good lookup!

😉

Multiply two fields from different tables

Oleg_Troyansky — Fri, 24 Apr 2009 09:43:32 GMT

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

Multiply two fields from different tables

yblake — Fri, 24 Apr 2009 14:27:38 GMT

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.

Multiply two fields from different tables

rbecher — Fri, 24 Apr 2009 16:30:06 GMT

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.

Multiply two fields from different tables

rwunderlich — Sat, 25 Apr 2009 02:29:08 GMT

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

Multiply two fields from different tables

rwunderlich — Sat, 25 Apr 2009 02:33:11 GMT

Oleg,

Great wiki entry. Thanks for posting it!

-Rob

Multiply two fields from different tables

Oleg_Troyansky — Sat, 25 Apr 2009 02:39:51 GMT

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

Multiply two fields from different tables

Fri, 01 May 2009 04:07:29 GMT

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

Multiply two fields from different tables

bullish35 — Fri, 08 Oct 2010 20:01:21 GMT

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