ログデータあるある


購入や訪問が発生するたびにデータが追加されるログデータですが、その日に購入や訪問が発生しないとそもそもデータとして入ってきません。

例えば、下記のようなログデータの場合、2021年1月1日にはa-1000・a-1001・a-1002の3台の機械で売上が発生し、2021年1月2日にはa-1003・a-1004・a-1005の3台の機械で売上が発生しています。

date	machine_id	sales
2021/1/1	a-1000	100
2021/1/1	a-1001	200
2021/1/1	a-1002	300
2021/1/2	a-1003	400
2021/1/2	a-1004	500
2021/1/2	a-1005	600

このログデータを下記のマスタと[machine_id]で結合すると、

machine_id	machine_name
a-1000	0号機
a-1001	1号機
a-1002	2号機
a-1003	3号機
a-1004	4号機
a-1005	5号機

こんな感じになります。


しかし上記は当然のことながら、ログデータに存在しない(=つまり売上がなかった機械)はその日付上ではでてきません。

売上がなかった機械は売上0として表示させたい場合、どうすれば良いでしょうか？

date	machine_id	machine_name	sales
2021/1/1	a-1000	0号機	100
2021/1/1	a-1001	1号機	200
2021/1/1	a-1002	2号機	300
2021/1/1	a-1003	3号機	0
2021/1/1	a-1004	4号機	0
2021/1/1	a-1005	5号機	0
2021/1/2	a-1000	0号機	0
2021/1/2	a-1001	1号機	0
2021/1/2	a-1002	2号機	0
2021/1/2	a-1003	3号機	400
2021/1/2	a-1004	4号機	500
2021/1/2	a-1005	5号機	600

今回はこちらについて考えていきます。


ログデータに存在しないが、マスタに存在するIDを表示する

ログデータとマスタを[machine_id]で結合してしまうと、ログデータの日付に対してマスタに記載の[machine_id]を紐付けられません。なので今回は[machine_id]ではなく、[date]で結合することを考えてみます。

1. 日付マスタを準備する
   

   date

   2021/1/1

   2021/1/2

   
   ログデータに入っている日付をすべて列挙した日付マスタを作成します。
   
   
2. マスタと上記の日付マスタを結合キー「1」で結合する
   
   
   これでマスタの各レコードに対して、日付マスタのすべての日付が紐付けられます。
   

   machine_id	machine_name	key	date
   a-1000	0号機	1	2021/1/1
   a-1001	1号機	1	2021/1/1
   a-1002	2号機	1	2021/1/1
   a-1003	3号機	1	2021/1/1
   a-1004	4号機	1	2021/1/1
   a-1005	5号機	1	2021/1/1
   a-1000	0号機	1	2021/1/2
   a-1001	1号機	1	2021/1/2
   a-1002	2号機	1	2021/1/2
   a-1003	3号機	1	2021/1/2
   a-1004	4号機	1	2021/1/2
   a-1005	5号機	1	2021/1/2

   
   
   
3. 日付マスタとログデータを[date]をキーにして結合する
   
   
   
   
4. 値を計算する計算式を作成する
   
   iif([Machine Id (Log)]=[Machine Id],[Sales],0)
   
   結合時に[machine_id]を指定していないので、ログデータとマスタの[machine_id]が一致した問だけ値を返す計算式を作成します。他にもディメンションがあれば、適宜条件式に追加しましょう。
   
   
5. 完成！