我有下表：
交易对手表
Counterparty
foo
boo
fizz
buzz
huzz
表1数据：
Counterparty Commodity DealType StartDate FloatPrice MTMValue
foo bar Buy 07/01/2024 18.73 10
fizz bar Buy 09/01/2024 17.12 10
fizz bar Buy 10/01/2024 17.76 10
fizz bar Buy 11/01/2024 18.72 10
fizz bar Buy 12/01/2024 19.47 10
foo ab cred Buy 01/01/2025 20.26 10
boo baz Sell 01/01/2025 21.04 10
boo baz Sell 01/01/2025 22.23 10
fizz baz Sell 01/01/2025 11.89 10
fizz baz Sell 01/01/2025 12.33 10
foo baz Sell 01/01/2025 14.78 10
foo baz Sell 01/01/2025 18.23 10
boo rins Sell 01/01/2025 16.43 10
boo ab cred Sell 01/01/2025 12.21 10
表 2 数据
Counterparty Commodity DealType StartDate FloatPrice MTMValue
foo bar Buy 07/01/2024 18.73 10
fizz bar Buy 09/01/2024 17.12 10
fizz bar Buy 10/01/2024 17.76 10
fizz bar Buy 11/01/2024 18.72 10
fizz bar Buy 12/01/2024 19.47 10
foo ab cred Buy 01/01/2025 20.26 10
boo baz Sell 01/01/2025 21.04 10
boo baz Sell 01/01/2025 22.23 10
fizz baz Sell 01/01/2025 11.89 10
fizz baz Sell 01/01/2025 12.33 10
foo baz Sell 01/01/2025 14.78 10
foo baz Sell 01/01/2025 18.23 10
boo rins Sell 01/01/2025 16.43 10
boo ab cred Sell 01/01/2025 12.21 10
举例来说，表 1 和表 2 完全相同。实际上，它们的结构相同，但最后一列的值不同。
我如何编写查询来基于这 3 个表创建如下所示的表：
商品 = 条形码
Counterparty Table1 Sum Table2 Sum
foo 10 10
boo 0 0
fizz 40 40
buzz 0 0
huzz 0 0
我将在桌子上安装一个切片机来筛选商品
您可以创建一个暗淡的表
在 4 个表之间创建关系。
然后使用新 dim 表中的商品列作为过滤器并创建两个度量