Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Can you multiply 2 numbers from different tables in an expression?

Hey there!

So I've got a question about doing math on data from 2 different tables. I've got table A and table B. Both have a LocationID, Timepoint, Reporting Metric (named MetricA_ID in Table A and ScheduleB_ID in Table B) and value. I want to take a row where timepoint and location id are equal and multiply valueA * valueB. I can't seem to find a way in the expression syntax to do this. The final result I would like is a Sum of all of the multiplications. Here's a look at the tables:

Table A

LocationIDTimepointMetricA_IDValueA
62012-12-31 19:00:00.000366
202012-12-31 20:00:00.000367
252012-12-31 21:00:00.000368

Table B:

LocationIDTimepointScheduleB_IDValueB
62012-12-31 19:00:00.000425
202012-12-31 20:00:00.000426
252012-12-31 21:00:00.000427

So what I'm looking for is an expression that per row I can do (Where LocationID=LocationID and TimePoint=TimePoint and  MetricA_ID = 36 and ScheduleB_ID= 42, ValueA * ValueB).

I would like to then take all of those values:

30

42

56

and add them all together to get 128. Is there any way in an expression to do this? If loading the data a different way is the only way, I work through that. But I'd really like to keep the tables separated. Thanks!

1 Solution

Accepted Solutions
swuehl
MVP
MVP

Since the two tables are already linked by LocationID and Timepoint, you can also just use this in a textbox:

=Sum(ValueA*ValueB)

IF you need to filter Metric and schedule:

=Sum({<MetricA_ID = {36}, ScheduleB_ID = {42}>} ValueA*ValueB)

View solution in original post

8 Replies
sunny_talwar

May be this: Sum(ValueA * ValueB) with LocationID and TimePoint as dimensions

Capture.PNG

Not applicable
Author

Unfortunately we can't just do a join on the 2 tables. There are several formulas throughout the application that use a lookup by MetricID & ScheduleID so we can't say that where MetricA_ID=36 ScheduleB_ID=42 concatinate the row. We have another calculation where MetricA_ID=8 and ScheduleB_ID=42. We could create a new row using the example you showed above with those 2 ID's but we would like to avoid putting them together into the same table.

swuehl
MVP
MVP

Since the two tables are already linked by LocationID and Timepoint, you can also just use this in a textbox:

=Sum(ValueA*ValueB)

IF you need to filter Metric and schedule:

=Sum({<MetricA_ID = {36}, ScheduleB_ID = {42}>} ValueA*ValueB)

Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

Watch for performance - on a large data set, multiplying fields from different tables is known as notoriously slow.

Cheers,

Oleg Troyansky

Upgrade your Qlik skills at the Masters Summit for Qlik - coming soon to Milan, Italy!

Not applicable
Author

This is exactly what we needed! Our data is coming in correct from separate tables. I've got another question to ask if you can help, with the filtering:

=Sum({<MetricA_ID = {36}, ScheduleB_ID = {42}>} ValueA*ValueB)


How would I multiply two ValueA with different MetricA_IDs? If I do the following:

=Sum({<MetricA_ID = {36}, MetricA_ID = {38}>} ValueA*ValueA)


It multiplies the second metric by it's self and adds that together. Is there a way to say give me 2 values that are linked by LocationID and Timepoint, but have different MetricA_IDs and multiply them together?

sunny_talwar

May be this:

=Sum({<MetricA_ID = {36}>} ValueA) * Sum({<MetricA_ID = {38}>} ValueA)

Not applicable
Author

I don't think that's what we're looking for, we don't want to sum together all of the MetricA_ID=36 and then multiply that the sum of all MetricA_ID=38 we want to multiply ID=36 by ID=38 when Timepoint and Location are equal

swuehl
MVP
MVP

Maybe

Aggr(

Only({<MetricA_ID = {36}>} ValueA) * Only({<MetricA_ID = {38}>} ValueA)

, Timepoint , LocationID

))

edit: Corrected small typo