デジタル推進課

KNIME・Excel Macro・Power Automateなど日々の業務で使用できる自動化ツールを中心に書き綴ります

KNIME - VLOOK UP を覚える ~Joiner~

 

 

はじめに

さて、本格的なNodeの説明に移りたいと思います。まずはReport作成で必須・KNIMEの得意技、Excel でいうVLOOKUPを覚えます。Nodeの名前は"Joiner"と言います。

 

 

今回のテーマ ~Joiner~

 社員表は前回までのデータをそのまま使用したいと思います。新しいNode "Modify Time"が時間データを消すために入っていますが、今回は深入りしません。

f:id:makkynm:20200420235833p:plain

Joiner

 

覚えてほしいこと

  • くっつけ方は4種類
  • 元データを上に、マスタを下にくっつけて、Left Outer Join
  • データ数で結果を検証 

 

やりたいこと

今回例として、「勤怠表」に、社員番号をキーにして「社員表」記載の情報(名前・部署・勤務地・生年月日・入社日)をくっつけてみましょう。

実際に下記のようなExcelを作ってみて試してみてください。

f:id:makkynm:20200421091007p:plain

やりたいこと

前準備 Modify Time

前準備として、時間を消すために入れている"Modify Time" は今回のテーマではないので、Confirm画面とConfigureの画面を貼っておきます。

Confirm - Before After

f:id:makkynm:20200421001718p:plain

Before After(Modify Time)
Configure

Remove Timeを選択することで、自動的に作られてしまった時間を削除して、日付のみにすることができます。

f:id:makkynm:20200421001017p:plain

Modify Time Configure

 さて、Inputが集まったので早速行っていきましょう。

 

Joinerの使い方

Workflow

f:id:makkynm:20200420235833p:plain

Joiner

Confirm - Before After

4.Confirm の結果を先に見てみましょう。勤怠表にVLOOKUPで当てたかのように社員表のデータが追加されていることが分かります。

f:id:makkynm:20200421234727p:plain

Joiner

Configure

大きく分けて4Stepあります。

f:id:makkynm:20200421235113p:plain

Joiner Configure - Step1, 2

f:id:makkynm:20200422000122p:plain

Joiner Configure - Step3, 4

 

Step1 - Join Mode

 これがものすごく重要です。くっつけ方は4種類あります。KNIMEでは、上からのInputをLeft、下からのInputをRightと定義しています。Outerがつくと、保持するというような意味がつきます。

ベン図で書くと下図のような形になります。上と下のテーブルがそれぞれどう保持されるかがこれで分かります。

f:id:makkynm:20200422085246p:plain

くっつけ方は4種類

図出典:3.0 Data Sources Tab

分かりにくいですかね??もし分からない!って人は、これだけ覚えてください。

元データを上に、マスタを下にくっつけて、Left Outer Join」 

ここでの注意点として、マスタの重複がないことを必ず確認してください。

ちなみにInner Joinはよく、マスタあるものだけを残したいといった場合に使います。

Full Outer Joinは、多対多の結合で使用します。

 

Step2 - Joining Column

次にKeyを設定します。VLOOKUPでいう、"検索値"の部分です。KNIMEでは、複数選択が可能です。ここでは社員番号を基準にVLOOKUPするので、社員番号にあたるコラムをTop Input、Bottom Inputのそれぞれから選択してあげれば完了です。

ここでの注意点として、必ずデータ型を一致させてください。よくあるミスとして、IDがInt(数値)として取り込まれている場合とString(文字列)として取り込まれているものを突き合わせようとしてJoinできなかったりします。

 

Step3 - Column Selection

次に残すコラムを設定します。まず上方で選択可能なタブ ” Column Selection ”を選択します。するとコラム選択の画面に移るので、各Inputテーブルから、Joiner実行後に残したいコラムをIncludeの方に移してください。

Step4 - Joining Column Handling

最後に、デフォルトでチェックが入っている④の部分のチェックを外します。

この機能をチェックすると、自動的にKeyとして使ったコラムを削除してしまいます。感覚的に、Step3で除いてしまった方が分かりやすいので、ここではすべてチェックを外してしまいましょう。

以上でJoinerの設定は終了です!

 

Confirm - 検証 Validation

最後にデータをExcelで見たときに、何かおかしい!データが足りない!ということがたまに発生します。その時私はよくJoinerを疑います。Joinerは非常に便利なのですが、正確に使わないと、データがおかしくなる原因になることが多いので、必ず検証作業を行いましょう。

検証方法はいたって簡単で、「データ総数を比べる」だけです。データ総数はConfirmの上から確認でき、行の総数(Rows: 12)列の総数(Columns: 5)が一目で確認できます。特にRowsの数を見てください。

f:id:makkynm:20200423084319p:plain

Confirm - 検証 Validation

今回のような、「Left Outer Join」を使用した場合、「上のテーブルの総数とJoiner後の行数が同じか」を確認すればOKです。今回の場合、下図のような形です。Rowsが同じ数になっていることが分かります。

もしここの数字が増えていた場合、マスタの重複を疑ってください。よくあるミスです。

f:id:makkynm:20200423092612p:plain

Confirm - 検証 Validation

 

 ちょっと一言

Joinするときの注意点

Joinするとき、マスタテーブルは必ず重複がないようにしてください。そうでないと、Join後に「なぜだかデータが増えている」ということになります。

マスタの重複を取り除くには、Joinerにつなぐ前に、 「Duplicate Row Filter」や「GroupBy」を使用することをお勧めします。

f:id:makkynm:20200422091002p:plain

重複を取り除く
Joinerのよくあるエラー

上でも書きましたが、Joinするときのよくあるエラーとして、データ型の不一致があります。必ずキーに設定するコラム(今回は"社員番号"、VLOOKUPでいう"検索値")のデータ型は揃えてください。

このようなエラーが起こらないためにも、新しいデータをImportしたら、データ型を変換して整える癖をつけておくといいです。

 

おわりに

Joinerが分かれば、KNIMEを使う幅がかなり広がります。ExcelのVLOOKUPはデータが重くなると、処理に限界があると思うので気軽にKNIMEが使えるようになると一気に日々の業務スピードが上がると思うので、ぜひマスターしてみてください。

 

 

余談

Joinerにピッタリな、組み合わせ感のあるおススメ映画は「最強のふたり」です!

個人的に1番好きな映画です!共通点がゼロの、全身マヒの富豪とスラム出身の黒人青年が徐々に最高の友人に変わっていくという物語です。富豪の価値観と黒人青年の人間味が好きなんです。これが実話っていうのももう最高です。

最強のふたり (字幕版)

最強のふたり (字幕版)

  • 発売日: 2013/11/26
  • メディア: Prime Video
 
最強のふたり (字幕版)

最強のふたり (字幕版)

  • 発売日: 2013/11/26
  • メディア: Prime Video
 

www.youtube.com

参考リンク