はじめに

Qlik Senseでは取り込んだ複数テーブル間は共通の名称を持つ項目をキーとして自動的に紐付けられ、以下の様な形でデータモデルが作成されます。

image

ただ、演算や変換処理を行うためや管理性を高める目的、もしくはパフォーマンス上の効率化の観点から複数のテーブルを一つのテーブルに結合する処理を行う場合があります。

ここではそういったことを行うためのApplyMap, Lookup, Join, Keepといった処理をそれぞれ見ていきたいと思います。ではまず準備として、以下の国データを含んだテーブルを作成します。

国テーブル:
Load * Inline [
  国ID,国コード,国名
  1,JP,日本
  2,US,アメリカ合衆国
  3,CN,中国
  4,GB,イギリス
];

image

値のルックアップ

ApplyMap

ApplyMapはいわゆるルックアップを行うために利用します。「キー」と「値」の2列を含んだMappingテーブルを事前に作成し、「キー」を持つ他のテーブルデータの取込み時にこのMappingテーブルを参照して「値」を取得することができます。

ここでは、追加する顧客テーブルに「国ID」を含んでおり、この値を「国名」に置き換える例を扱います。では、既に作成済みの「国」テーブルから、「国ID」と「国名」の列をそれぞれ「キー」と「値」としたMappingテーブルを以下の形で作成します。

Map_Country:
Mapping Load 国ID, 国名
Resident 国テーブル;

Loadステートメントの先頭に「Mapping」を付加することでMappingテーブルを作成することができます。このテーブルはルックアップを行うために一時的に作成されるテーブルで、ロード処理が完了すると自動的に消去されます。

次に、以下の形で顧客テーブルを追加します。

顧客テーブル:
Load
  ID,
  顧客,
  ApplyMap('Map_Country',国,Null()) AS 国名
Inline [
  ID,顧客,国
  1,顧客A,1
  2,顧客B,3
  3,顧客C,6
  4,顧客D,2
];
Drop Table 国テーブル;

ここで、ApplyMapの関数を使い、作成したMappingテーブルを参照して、国のキーから国名を取得する処理を行っています。

  • ApplyMap(‘Mappingテーブル名’, キー名, デフォルト値)

Mappingテーブル名は「’ (シングルクオート)」で囲み、キー名はクオートで囲まずにこのテーブル自身のキー名(ここでは「顧客テーブル」の「国」)を指定します。また、マッチするキーが無かった場合のデフォルト値としてここでは「Null(ヌル値)」を指定しており、必要に応じて文字列などの指定が可能です。(デフォルト値を指定しない場合にはキー名がそのまま返されます。)

これらの処理を実行すると、以下の内容の「顧客テーブル」が作成され、国のコード値から国名に変換されていることが確認できます。

image

このApplyMapはこの後ご説明しますLookupやJoinなどの処理と比べてパフォーマンスに優れますので、ApplyMapで対応できる処理についてはApplyMapの利用が推奨されます。

Lookup

先ほどのApplyMapでは、「国テーブル」から「キー」と「値」の2列を含んだMappingテーブルを作成して参照しましたが、このLookupを利用すると直接「国テーブル」を参照することができます。

顧客テーブル:
Load
  ID,
  顧客,
  Lookup('国名','国ID',国,'国テーブル') AS 国名
Inline [
  ID,顧客,国
  1,顧客A,1
  2,顧客B,3
  3,顧客C,6
  4,顧客D,2
];

Drop Table 国テーブル; 

Lookupの引数については以下となり、「参照元テーブルのキー名」のみは「’ (シングルクオート)」で囲まない点に注意が必要です。

  • Lookup(‘参照先テーブルの取得値を含む列名’ ,’参照先テーブルのキー名’,  参照元テーブルのキー名, ‘参照先テーブル名’)

Join

次に、2つのテーブルの結合を行うのがJoinになります。Joinを行うには、以下の形でLoadステートメントの先頭に「Join(結合先のテーブル名)」を指定します。

Join(国テーブル)
顧客テーブル:
Load * Inline [
  ID,顧客,国ID
  1,顧客A,1
  2,顧客B,3
  3,顧客C,6
  4,顧客D,2
];

この処理を実行すると、以下の形で結合された一つのテーブルが作成されます。結合のキーは両テーブルに存在する同名の項目が利用され、複数の項目をキーとして結合することも可能です。(結合を行ったキー項目の「国ID」は不要となるため、上記処理後に「Drop Field 国ID;」のステートメントで削除することができます。)

image

Joinに特に指定を行わない場合には、「Outer Join」として処理され、2つのテーブルに存在する全ての行が結合後のテーブルに含まれます。その他に、以下のJoinの方式を指定することが可能です。

  • Inner Join: 両方のテーブルに共通して存在する行のみが結合後のテーブルに含まれます。

image

  • Left Join: 左側(1つ目)のテーブルに存在する行のみが結合後のテーブルに含まれます。

image

  • Right Join: 右側(2つ目)のテーブルに存在する行のみが結合後のテーブルに含まれます。

image

Keep

KeepもJoinと同様にOuter, Inner, Left, Rigftを指定して利用し、同様の考え方で行が残されます。Joinと異なる点は、Joinではテーブルが結合されましたが、Keepではテーブルは分割されたままとなります。

例えば、以下の形でRight Keepを指定します。

Right Keep(国テーブル)
顧客テーブル:
Load * Inline [
  ID,顧客,国ID
  1,顧客A,1
  2,顧客B,3
  3,顧客C,6
  4,顧客D,2
];

 

その結果、Joinと異なり以下の形でテーブルは2つ作成されます。

image

ただし、Right Joinと同様に右側(2つ目)のテーブルに当たる「顧客テーブル」に存在する行がのみが残されます。

[顧客テーブル]

image

[国テーブル]

image

まとめ

ここでは、複数のテーブルを一つのテーブルに結合するために利用される、ApplyMap, Lookup, Join, Keepの処理をご説明しました。