複数条件で値を取得

[匿名]

Up

1

Down

::

みなさまのお知恵をお借りしたいです！
‘————————————————
テーブルが 2つあります。
<勤務データ>
名前 時間数 該当日
A 8 2020/03/14
B 10 2020/03/14
C 6 2020/03/14
A 9 2020/03/15
B 8 2020/03/15
C 7 2020/03/15

<昇給データ>
名前 時給 該当日
A 1000 2020/03/14
B 1100 2020/03/14
C 1050 2020/03/14
B 1200 2020/03/15

‘————————————————
勤務データテーブルに以下の様に、昇給日を参照したデータを取得したいのですが、うまくいきません。
<勤務データ>
名前 時間数 該当日 時給
A 8 2020/03/14 1000
B 10 2020/03/14 1100
C 6 2020/03/14 1050
A 9 2020/03/15 1000
B 8 2020/03/15 1200
C 7 2020/03/15 1050
※ B さんは、2020/03/15 に昇給しているので、3/14 までは、1100 で、3/15 は 1200 を拾いたい。
‘————————————————
<やってみたこと>
列 = CALCULATE (
SUM(‘昇給データ'[時給]),
FILTER(‘昇給データ’,
‘昇給データ'[名前]=’勤務データ'[名前] &&
‘昇給データ'[該当日]<=’勤務データ'[該当日]
)
)
を行いましたが、うまく取得できず、、、
名前 時間数 該当日 列
A 8 2020年3月14日 1000
B 10 2020年3月14日 1100
C 6 2020年3月14日 1050
A 9 2020年3月15日 1000
B 8 2020年3月15日 2300 ★ 合算されてしまう。
C 7 2020年3月15日 1050

lookupvalue でも、”>=”&’勤務データ'[該当日] はエラーになるので、他に算出可能な計算式はありますか？

[hrkasno]

Up

1

Down

::

必ず昇給する前提なら、
SUM(‘昇給データ'[時給])の代わりに
MAX(‘昇給データ'[時給])で取得できそう…ですが、
減給も想定すると もう少し工夫が必要ですね。

[匿名]

Up

0

Down

::

@hrkasno さん
返答ありがとうございます。
仰せの通り、昇給する前提なら MAX でよいのですが。。。

[hrkasno]

Up

1

Down

::

@neko-daisuki さん

昇給データに、時給適用期間の終了日があれば良いのでは？と考えました。

下記「D-昇給テーブル２」テーブルの「END日」を計算するために
中間テーブルをいくつか作ってます。

昇給データをクロス結合して、
自分より未来の昇給データがあれば、その該当日を
自分より未来の昇給データがなければ、日付の最大値(9999年12月31日)を
END日にしました。

――――――――――

<新しいテーブル>
A-昇給データの複製 =
SELECTCOLUMNS(‘昇給データ’,
“名前2”, [名前],
“該当日2”, [該当日],
“時給2”, [時給]
)

<新しいテーブル>
B-wkテーブル1 =
FILTER(
CROSSJOIN(‘昇給データ’,’A-昇給データの複製’),
[名前]=[名前2] &&
[該当日]<=[該当日2]
)

<新しいテーブル>
C-wkテーブル2 =
SELECTCOLUMNS(‘B-wkテーブル1’,
“名前”, [名前],
“該当日”, [該当日],
“END日候補”,IF( [該当日]<[該当日2],
[該当日2],
DATEVALUE(“9999/12/31”)
),
“時給”, [時給]
)

<新しいテーブル>
D-昇給テーブル２ =
SUMMARIZE(‘C-wkテーブル2’,
[名前],
[時給],
[該当日],
“END日”, MIN(‘C-wkテーブル2′[END日候補])
)

<新しい列>
適用時給 =
CALCULATE (
SUM(‘D-昇給テーブル２'[時給]),
FILTER(‘D-昇給テーブル２’,
‘D-昇給テーブル２'[名前]=’勤務データ'[名前] &&
‘D-昇給テーブル２'[該当日]<=’勤務データ'[該当日] &&
‘D-昇給テーブル２'[END日] > ‘勤務データ'[該当日]
)
)

――――――――――

これはSQL的なアプローチなので 他にもメジャーを使ったスマートなやり方があるのかもしれませんが、、
詳しいベテラン勢にまかせます。。私も教えて欲しいです笑

[匿名]

Up

0

Down

::

@hrkasno さん
ありがとうございます！
中間テーブルで、END日作成は思いつきませんでした。。。
上記参考に作成したところ、問題なく、あと、汎用的に利用できるツールとなりました！
ほんとに助かりました。今後とも、よろしくお願いいたします。

[投稿者]

投稿