データの関連付け

データの関連付け

Pocket

QlikViewでは複数のデータを取り込むと、自動的に関連付けがおこなわれます。
たとえば、以前ご紹介したウィザードを使わないデータの取り込みでも、地区や都道府県のデータが自動で関連付けられたかと思います。
そのためデータを取り込めば、すぐに分析をはじめられる訳ですが、場合によってはこの関連付けが意図したとおりに動作しないことがあります。
ここでは、QlikViewでデータの関連付けを制御する方法について説明いたします。

まず、QlikViewがデータの関連付けをどのようにおこなっているかというと、これは非常に単純な話で、項目名が同じもので自動的に関連付けをおこなっています。

そのため、QlikViewでは同じ意味を持つデータはかならず同じ項目名に、違う意味を持つデータはかならず違う項目名にしなければなりません。
たとえば、担当者のIDをあらわす項目の名前が「担当者番号」「担当者ID」「担当者NO」などと違う表現になっていた場合、データの関連付けはおこなわれません。
逆に営業マンの氏名をあらわす項目と、顧客の氏名をあらわす項目が、両方とも「氏名」という名前だった場合、データが意図せず関連付いてしまいます。

ここでは、QlikView側で項目名を変更し、データの関連付けを正しくおこなう方法について説明いたします。

ファイルのダウンロード
ロードスクリプトの作成
項目名の変更1(意図しない関連付けがおこなわれるケース)
項目名の変更2(意図した関連付けがおこなわれないケース)
テーブル名の変更
複合キーとSyntheticテーブル
振り返り
最後に…
補足説明


ファイルのダウンロード

ここでは以下のファイルを使用しますので、ダウンロードしてください。
associate-data.zip
Zip形式ですので、ダウンロード後解凍してください。
associate-data-101売上実績.xlsと、人口.xlsという2つのExcelファイルが含まれています。


ロードスクリプトの作成

ここでは、売上実績.xlsを使用してデータの関連付けについて確認してみましょう。このExcelファイルには以下の3つのシートが含まれています。

「売上明細」シート
売上の明細データが格納されています。
associate-data-102
「商品」シート
商品マスターの情報が格納されています。
associate-data-103
「担当者」シート
担当者マスターの情報が格納されています。
associate-data-104
それではデータを取り込んでみましょう。
まずはファイルを新規に作成し、[ロードスクリプトの編集]画面を起動します。

QlikViewを起動してスタートページを表示します。
[ファイル]→[新規作成]を選択します。
ウィザードが起動したら[キャンセル]ボタンをクリックしてウィザードを終了します。
associate-data-105
空のドキュメントが表示されます。
ツールバーの[ロードスクリプトの編集]ボタンをクリックします。
associate-data-106
ロードスクリプトで「SET」の下の行をクリックして、最下行にカーソルをあわせます。
この状態で画面右側の[テーブルファイル]ボタンをクリックします。
associate-data-107
「売上実績.xls」を選択します。
associate-data-108
[テーブル]から[売上明細$]を選択し[終了]ボタンをクリックします。
associate-data-109Excelファイル(xlsファイル)を取り込んだ場合、シート名の末尾に「$」を付けたものがテーブル名になります。
テーブル名の変更方法も後半で説明いたします。

同様にして、こんどは商品のデータを取り込みます。
associate-data-110
[テーブル]から[商品$]を選択して[終了]ボタンをクリックします。
associate-data-111
ロードスクリプトが作成できました。[OK]ボタンをクリックします。
associate-data-112


項目名の変更1(意図しない関連付けがおこなわれるケース)

それでは、データを取り込んでみましょう。
[リロード]ボタンをクリックし、ファイルを保存してください。
associate-data-201
associate-data-202
associate-data-203
[ロードスクリプトの進捗]画面を確認すると、「$Syn 1 …」という表示がでています。
詳細は後ほど説明いたしますので、ひとまず[閉じる]ボタンで画面を閉じてください。
associate-data-204
データの関連付けを確認するには、テーブルビューアーを使用します。
ツールバーから[テーブルビューアー]ボタンをクリックしてください。
※ツールバーが表示されていない場合は、[表示]→[ツールバー]→[デザイン]を選択して表示させてください。
associate-data-205
テーブルビューアーが表示されました。
線で繋がれているところが、データの関連付けがおこなわれている項目です。
「商品番号」と「更新日時」、2つの項目で関連付けられていることが分かります。
associate-data-206
上の画面と表示が違う場合は、画面右上で[ソーステーブルビュー]を選択してください。
associate-data-207[ソーステーブルビュー]は取り込んだテーブルのみを表示するものです。

[内部テーブルビュー]を選択すると以下の画面が表示されます。
associate-data-209[内部テーブルビュー]はQlikViewが内部的に保持するテーブルを含め、すべてのテーブルを表示するものです。
[内部テーブルビュー]を見ると「$Syn 1 Table」というテーブルが自動的に作成されていることが分かります。
これは複数の項目で関連付けがおこなわれたときに、内部的に作られるテーブルなのですが、詳細は後述いたします。

[ソーステーブルビュー]で、あらためて関連付けを確認してみます。
商品と売上明細にある「更新日時」という項目は、データが更新された日時を保持しています。
つまり、「更新日時」はたまたま項目名が同じだっただけで、違う意味を持つ項目です。
このままだと、商品番号が同じで、かつ更新日時が(たまたまぴったり)同じだったデータのみが関連付けられるため、修正が必要です。
associate-data-208現状が確認できたら[OK]ボタンをクリックして、[テーブルビューアー]を閉じます。

データの関連付けを修正します。[ロードスクリプトの編集]画面を開いてください。
「更新日時」の項目名を変更します。今回は売上明細の方の「更新日時」を「売上更新日時」に変更します。
変更前
更新日時
変更後
更新日時 as 売上更新日時
associate-data-210もちろん商品の方の「更新日時」も変更しても構いません。
設定後[OK]ボタンをクリックします。

再度リロードを実行してください。こんどは「$Syn1…」の表示はでてきません。
associate-data-211
再度[テーブルビューアー]を確認してみます。
associate-data-212
「更新日時」での関連付けがなくなり「商品番号」のみで、ただしく関連付けることができました。
associate-data-213


項目名の変更2(意図した関連付けがおこなわれないケース)

こんどはまた別のケースを確認してみましょう。
追加でデータを取り込みますので、[ロードスクリプトの編集]画面を表示してください。
前回と同じようにデータを取り込みます。
associate-data-301
今回は「担当者」のデータを取り込んでください。
associate-data-302
[OK]ボタンをクリックして、データをリロードしてください。
associate-data-303
associate-data-304
[テーブルビューアー]で関連付けを確認してみましょう。
associate-data-305
担当者のデータが取り込まれていますが、関連付けがなにもおこなわれていません。
項目名をよく見ると、担当者の方は「担当者番号」、売上明細の方は「担当者NO」と、項目名が食い違っています。
確認できたら[OK]ボタンをクリックしてください。
associate-data-306
ロードスクリプトを以下のように変更します。
変更前
担当者NO,
変更後
担当者NO as 担当者番号,
associate-data-307もちろん、担当者の方で「担当者番号」→「担当者NO」に項目名を変更しても構いません。

データをリロードすると関連付けが、ただしくおこなわれます。
associate-data-308


テーブル名の変更

データの関連付けとは関係ありませんが、項目名の変更方法を覚えましたので、一緒にテーブル名の変更方法も覚えておきましょう。
テーブル名を変更するには、「LOAD」文の前に「テーブル名:」と追加します。

ここでは「売上明細$」テーブルを「売上明細」テーブルに変更します。
※Excelからデータを取り込むと、テーブル名はシート名の末尾に「$」を付けたものになります。ここでは末尾の「$」を削除してみます。

ロードスクリプトを以下のように変更します。
変更前
LOAD 納品日付,
変更後
売上明細:
LOAD 納品日付,
末尾の「:」コロンを忘れないでください。
associate-data-401
リロードしてください。
変更後のテーブル名は[ロードスクリプトの進捗画面]でも確認できます。
associate-data-402
[テーブルビューアー]の表示も変更されています。
associate-data-403
シートの余白を右クリック→[リストボックスの追加]を選択してみます。項目を選択する画面の表示も変更されています。
associate-data-404
テーブル名はデータの関連付けなどとは関係ありませんので、どのような名前を付けても動作は変わりません。
しかし、テーブル名は上図のようにさまざまな箇所に表示されますので、分かりやすい名前を付けるようにしてください。

ここまででデータの関連付けに関する操作は終了です。ドキュメントは閉じていただいて構いません。


複合キーとSyntheticテーブル

データの関連付けについてはここまでで終了ですが、途中で出てきた「$Syn1」というテーブルが何だったのか確認しておきましょう。
[テーブルビューアー]で[内部テーブルビュー]を選択した際、以下の画面が表示されました。
associate-data-209ここにある「$Syn 1 Table」は、Syntheticテーブル(シンセティックテーブル)と呼ばれるものです。
QlikViewは複数の項目で関連付けがおこなわれた場合、自動的にSyntheticテーブルと呼ばれる内部テーブルを作成します。
これは、自動的にかつ内部的に作成されるものなので、そのままにしておいても動作上、特に問題はありません。

しかし、Syntheticテーブルは本来必要ないテーブルですので、削除できるのであれば削除した方が、処理が効率的になります。
ここでは、Syntheticテーブルを削除する方法を確認してみましょう。

人口.xlsファイルの方を使用しますので、まずデータを確認してみましょう。
※このデータはThe World Bank(http://www.worldbank.org)がWeb上で公開しているデータに、独自の編集を加えたものです。
「人口」シートには、国ごと年ごとに人口のデータが格納されています。
associate-data-501
「平均寿命」シートには、国ごと年ごとに平均寿命のデータが格納されています。
associate-data-502つまり人口と平均寿命のデータは、「国」と「年」という2つの項目をキーとして関連付ける必要があります。

それでは、データを取り込んでみましょう。
[ロードスクリプトの編集画面]を開きます。
associate-data-503
「人口.xls」から「人口」のデータを取り込みます。
associate-data-504
associate-data-505
associate-data-506
同様にして「平均寿命」のデータも取り込みます。
associate-data-507
associate-data-508
ロードスクリプトが完成しました。[OK]ボタンをクリックします。
associate-data-509
データを取り込んでみましょう。
[ロードスクリプトの進捗]画面に、「$Syn 1…」とでてきました。
これはSyntheticテーブルが作成されたことをあらわしています。
associate-data-510
[テーブルビューアー]を確認してみましょう。
associate-data-511
「国」と「年」、複数の項目で関連付けられています。
associate-data-512
[内部テーブルビュー]を確認すると、Syntheticテーブルができています。
associate-data-513
Syntheticテーブルを削除しましょう。
複数の項目を1つのキー項目にまとめることで、Syntheticテーブルを削除できます。
ここでは、複数の項目から自動的に一意の連番を作成する、AutoNumberHash256関数を使用して、キー項目を作成してみます。

人口の方の「LOAD」文を以下のように編集します。
変更前
LOAD 国,
     年,
変更後
LOAD AutoNumberHash256(国, 年) as ID
     国,
     年,
associate-data-514AutoNumberHash256関数は自動的に連番を作成する関数です。項目を,(カンマ)区切りで複数指定すると、それらの項目の組み合わせが一意になるように自動的に連番を作成してくれます。

平均寿命の方の「LOAD」文を以下のように編集します。
変更前
LOAD 国,
     年,
     平均寿命
変更後
LOAD AutoNumberHash256(国, 年) as ID
     平均寿命
associate-data-515さきほどと同じようにAutoNumberHash256関数を追加します。また「国」と「年」は「人口」の方から読み込むため、こちらからは削除します。

リロードを実行します。
こんどは「$Syn 1…」の表示はでてきません。
associate-data-516
[テーブルビューアー]で[内部テーブルビュー]を確認すると、Syntheticテーブルが削除されていることが分かります。
associate-data-517


振り返り

ここではデータの関連付けについてご覧いただきました。以下に重要なポイントをまとめます。
  • QlikViewは項目名が同じもので自動的に関連付けをおこなう。
    そのため、同じ意味を持つデータはかならず同じ項目名に、違う意味を持つデータはかならず違う項目名にしなければならない。
  • 項目名の変更はロードスクリプトで以下の指定をおこなう。
    項目名 as 変更後の項目名
  • テーブル名の変更はロードスクリプトで以下の指定をおこなう。
    変更後のテーブル名:
    LOAD
  • Syntheticテーブル
    QlikViewは複数の項目で関連付けがおこなわれた場合、自動的にSyntheticテーブルと呼ばれる内部テーブルを作成する。Syntheticテーブルは削除した方が処理が効率的。
  • 複数のキー項目を1つのキー項目にまとめるには、AutoNumberHash256関数などを使用する。
    AutoNumberHash256(項目名1, 項目名2 ...)

今回作成したロードスクリプトは以下のとおりです。
※前半の「SET」文は省略しています。
※このスクリプトをコピーして使用する場合は、ファイルのパスを環境にあわせて変更してください。
売上明細:
LOAD 納品日付, 
     商品番号, 
     担当者NO as 担当者番号, 
     販売単価, 
     納品数量, 
     売上金額, 
     更新日時 as 売上更新日時
FROM
売上実績.xls
(biff, embedded labels, table is [売上明細$]);

LOAD 商品番号, 
     商品名, 
     商品名カナ, 
     更新日時
FROM
売上実績.xls
(biff, embedded labels, table is [商品$]);

LOAD 担当者番号, 
     担当者名, 
     担当者名カナ, 
     メールアドレス
FROM
売上実績.xls
(biff, embedded labels, table is [担当者$]);

LOAD AutoNumberHash256(国, 年) as ID,
     国, 
     年,
     人口
FROM
人口.xls
(biff, embedded labels, table is [人口$]);

LOAD AutoNumberHash256(国, 年) as ID,
     平均寿命
FROM
人口.xls
(biff, embedded labels, table is [平均寿命$]);


最後に…

ここでは、ロードスクリプトで項目名、テーブル名を変更する方法などをご覧いただきましたが、ロードスクリプトには他にもさまざまな機能や関数が用意されています。
ロードスクリプトを使用することで、さまざまなデータの加工ができます。

お疲れ様でした。


補足説明

以降の補足説明は、QlikViewの中級者~上級者向けに書かれたものです。QlikViewを使う上で、ここに書かれた知識は必須のものではありません。QlikViewについてさらに理解を深めたい中級者~上級者以外の方は、読み飛ばしていただいて構いません。

連番を作成する関数

本編では、複数の項目から一意の連番を作成するためにAutoNumberHash256関数を使用しましたが、連番を作成する関数には他にもさまざまなものがあります。

RowNo関数
RowNo()
RowNo関数は単純な連番を作成します。1つのテーブル内で連番を作成する場合に重宝しますが、単純に1から連番が振られていくだけですので、複数のテーブルを関連付けるためのキーとしては使用できません。

AutoNumber関数
AutoNumber(式)
AutoNumber関数は与えられた1つの式(項目名)に対して、一意の連番を振ります。
式(項目名)が同じであれば、連番もかならず同じ番号になるためキー項目を作成するのに使用できます。

AutoNumberHash128関数
AutoNumberHash128(式1, 式2 ...)
AutoNumberHash128関数は与えられた複数の式(項目名)に対して、一意の連番を振ります。

AutoNumberHash256関数
AutoNumberHash256(式1, 式2 ...)
AutoNumberHash256関数は与えられた複数の式(項目名)に対して、一意の連番を振ります。
内部な処理(128ビットのハッシュ値を使用するか、256ビットのハッシュ値を使用するか)を除けば、AutoNumberHash128関数と、AutoNumberHash256関数は同じ効果を持ちます。

AutoNumber関数とAutoNumberHash関数
AutoNumber関数でも連番を作成できますが、式(項目名)を1つしか指定できません。
複数の項目から一意の連番を作成したい場合、たとえば以下のように指定することで、国ごと年ごとに一意の連番を作成できます。
AutoNumber(国 & 年)
AutoNumber(国 & '-' & 年)
※&は文字列を連結する演算子です。

しかし、項目が複数ある場合は、AutoNumberHash128関数(AutoNumbeHash256関数)を使用した方が簡単です。
AutoNumberHash256(国, 年)

複数の項目から単一のキー項目を作成する際の補足

人口のデータの例で、今回の例では人口のシートからのみ「国」と「年」を読み込みました。(平均寿命のテーブルからは「国」と「年」を削除しました。)
associate-data-517今回のデータでは、人口のシートと平均寿命のシートに格納される「国」と「年」が完全に一致しているため、このようにしています。
しかし、実際のデータでは複数のテーブルに格納されるデータが完全に一致していないこともあり得ます。
たとえば、2011年のデータが平均寿命のシートにのみ格納されていた場合、今回の指定方法では2011年の「国」と「年」は読み込まれないことになります。

どちらかのシートにある「国」と「年」をすべて読み込むには、以下のようなロードスクリプトを作成します。
詳細は割愛しますが、機会があればこのブログで紹介したいと思います。
/* 人口のシートから、国と年のデータを読み込みます。
LOAD Distinctで重複したデータを読み込まないようにします。 */
マスター:
LOAD Distinct
     AutoNumberHash256(国, 年) as ID,
     国, 
     年
FROM
人口.xls
(biff, embedded labels, table is [人口$]);

/* 平均寿命のシートから、国と年のデータを読み込みます。
QlikViewでは同じ構造のテーブル(項目名がすべて同じテーブル)は、縦に連結されます。
このデータは上で読み込んだデータの下に追加されます。 */
LOAD Distinct
     AutoNumberHash256(国, 年) as ID,
     国, 
     年
FROM
人口.xls
(biff, embedded labels, table is [平均寿命$]);

//ここまででマスターが作成されます。

//人口のデータをロードします。
LOAD AutoNumberHash256(国, 年) as ID,
     人口
FROM
人口.xls
(biff, embedded labels, table is [人口$]);

//平均寿命のデータをロードします。
LOAD AutoNumberHash256(国, 年) as ID,
     平均寿命
FROM
人口.xls
(biff, embedded labels, table is [平均寿命$]);
テーブルの構造は以下のようになります。
associate-data-518これを見てお気づきかもしれませんが、結局もとのテーブル構造(Syntheticテーブルがある状態)と、同じような構造となります。

もとのテーブル構造(Syntheticテーブルがある状態)
associate-data-513言ってみれば、いま手動でおこなった作業は、QlikViewが自動でおこなったSyntheticテーブルの作成を、手動で再現したものになります。

もちろん、もとのテーブル構造(Syntheticテーブルがある状態)と、変更後のテーブル構造(手動で中間テーブルを作成した状態)で、パフォーマンス等も含めまったく同じであるとは言い切れませんが、複合キーを解決するために中間テーブルを作成している点は同じです。
そのため上のロードスクリプトはあまり実用性のないものなのですが、Syntheticテーブルがなぜ必要なのか、ご理解いただくために取り上げました。