8 Replies Latest reply: Jan 22, 2016 1:49 PM by Stefan Wühl RSS

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

    Matthew Brenner

      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!