Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

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

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

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)

8 Replies
MVP
MVP

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

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

Capture.PNG

Not applicable

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

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.

MVP
MVP

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

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)

MVP
MVP

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

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

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

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?

MVP
MVP

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

May be this:

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

Not applicable

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

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

MVP
MVP

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

Maybe

Aggr(

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

, Timepoint , LocationID

))

edit: Corrected small typo

Community Browser