Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
LocationID | Timepoint | MetricA_ID | ValueA |
---|---|---|---|
6 | 2012-12-31 19:00:00.000 | 36 | 6 |
20 | 2012-12-31 20:00:00.000 | 36 | 7 |
25 | 2012-12-31 21:00:00.000 | 36 | 8 |
Table B:
LocationID | Timepoint | ScheduleB_ID | ValueB |
---|---|---|---|
6 | 2012-12-31 19:00:00.000 | 42 | 5 |
20 | 2012-12-31 20:00:00.000 | 42 | 6 |
25 | 2012-12-31 21:00:00.000 | 42 | 7 |
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!
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)
May be this: Sum(ValueA * ValueB) with LocationID and TimePoint as dimensions
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.
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)
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!
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?
May be this:
=Sum({<MetricA_ID = {36}>} ValueA) * Sum({<MetricA_ID = {38}>} ValueA)
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
Maybe
Aggr(
Only({<MetricA_ID = {36}>} ValueA) * Only({<MetricA_ID = {38}>} ValueA)
, Timepoint , LocationID
))
edit: Corrected small typo