Qlik Trainingブログ

  • QlikView
  • データ準備
2013.07.16

年や月ごとに分かれたExcelファイルをまとめて取り込む方法

traffic-jam.jpg

Excelでデータを管理している場合、年や月ごとにファイルが分かれていることがあると思います。
QlikViewを使用すれば、複数のExcelファイルから一括でデータを取り込み、一つに統合できます。
ここではQlikViewを使用して、複数のExcelファイルを一つにまとめる方法を紹介いたします。

まだQlikViewをお持ちでない方は、ぜひこれを機にダウンロードしてみてください。
QlikViewは以下のページから無料でダウンロードできます。(要ユーザー登録)
QlikView Personal Editionのダウンロード

まず、処理のイメージを明確にしておきましょう。
手書きですみませんが下図をご覧ください。

concatenate-figure.png

このように、複数のExcelファイルを読み込み、読み込んだテーブルを縦に連結するイメージです。
これは、SQLで言うところのUNION(ユニオン)に相当する処理ですが、QlikViewではConcatenate(コンカチネート)と呼びます。

前述のとおり、年や月ごとに分かれたExcelファイルをまとめるために使用されることが多い機能ですが、この例に限らず複数のテーブルを一つにまとめる際に使用できます。
たとえば、2010年以前のデータがAccessにあり、2011年以降のデータがSQL Serverにあるときに、それらを一つのテーブルにまとめるときにも使用できます。

複数のテーブルを取り込んだ場合、QlikViewは同じ構造のテーブルを自動で判別し、一つのテーブルにまとめてくれます。
そのため、多くの場合はファイルが分かれていても単純にデータを取り込むだけですみます。
しかし、テーブルを一つにまとめるかどうかは、項目数と項目名によって判断されるため、データによっては手動で調整が必要な場合があります。

それでは、QlikViewが自動的にテーブルを連結する際の動作について、詳細を見ていきましょう。

ファイルのダウンロード

ここでは以下のファイルを使用しますので、ダウンロードしてください。
concatenate.zip
Zip形式ですので、ダウンロード後解凍してください。
以下ファイルが含まれています。

concatenate-101.png

売上実績.xls
一つ目のチュートリアルで使用するデータです。
2008年と2009年のデータが、二つのシートに分かれて格納されています。データの件数はそれぞれ60件ずつです。(12ヶ月分 * 商品分類が5つずつ。)
また、三つ目のシートには商品分類のデータが格納されています。

concatenate-102.pngconcatenate-103.pngconcatenate-104.png

テーブルの連結1(単純な連結)

それではデータを取り込んでみましょう。
まずはファイルを新規に作成し、[ロードスクリプトの編集]画面を起動します。

concatenate-105.png

ロードスクリプトの最下行にカーソルをあわせて、[テーブルファイル]ボタンをクリックします。

concatenate-106.png

「売上実績.xls」を選択します。

concatenate-107.png

[テーブル]が「売上2008$」であることを確認して、[終了]ボタンをクリックします。

concatenate-108.png

同様にして「売上2009」シートと、「商品分類」シートのデータも取り込みます。

concatenate-109.pngconcatenate-110.png

ロードスクリプトは以上で完成です。[OK]ボタンをクリックします。

concatenate-111.png

データを取り込んでみましょう。
[リロード]ボタンをクリックします。

concatenate-112.png

ファイルを保存してください。

concatenate-113.png

[ロードスクリプトの進捗]画面を確認してください。
下図の赤枠の表示は「売上2008$」テーブルに、「売上2009$」テーブルが連結されたことをあらわしています。
データの件数も二つのテーブルをあわせた件数(120件)となっています。

concatenate-114.png

テーブルビューアーでも確認してみましょう。

concatenate-115.png

売上関連のテーブルが一つしかないことが確認できます。

concatenate-116.png

テーブル名にカーソルをあわせると、合計120件になっていることが、ここでも確認できます。

concatenate-117.png

また、テーブルビューアーを見て分かるとおり、テーブル名は一つ目に取り込んだテーブルの名前になるため、今回は「売上2008$」という名前になっています。

より適切なテーブル名に変更してみましょう。
[ロードスクリプトの編集]画面を起動し、テーブル名を指定します。

concatenate-118.png

リロードしてください。テーブル名が「売上」に変更されました。

concatenate-119.pngconcatenate-120.png

テーブルボックスを使用して、データを確認してみましょう。
シートの余白を右クリックし[シートオブジェクトの追加]→[テーブルボックス]を選択します。

concatenate-121.png

[すべて追加]ボタンをクリックして、すべての項目を選択します。

concatenate-122.png

[ロード順]ボタンをクリックして、項目の順序を調整します。
[OK]ボタンをクリックします。

concatenate-123.png

テーブルボックスが作成されました。
2008年と2009年、両方のデータが取り込まれていることが確認できます。

concatenate-124.png

データの取り込みは以上で完了ですが、どのようなときにテーブルが連結されて、どのようなときに連結されないのか、あらためて確認してみましょう。
まずはじめのポイントですが、前述のとおり重要なのは項目の”名前”であり、項目の指定順序は関係ありません。
このことを確認してみましょう。

[ロードスクリプトの編集]画面を起動します。
下図のように「商品分類番号」と「月」の指定順序を入れ替えます。

concatenate-125.png

リロードしてください。
項目の指定順序を変更しても、さきほどと同じようにテーブルが連結されます。

concatenate-126.png

つぎのポイントです。
項目数が異なる場合、テーブルは連結されません。
[ロードスクリプトの編集]画面を起動します。
下図のように「商品分類番号」をコメントアウトします。

concatenate-127.png

リロードしてください。
60件のテーブルが二つできていることが分かります。また、同じ名前の項目がいくつかあるため、Syntheticテーブルができています。

concatenate-128.pngconcatenate-129.png

さいごのポイントです。
(項目数が同じでも)項目名が異なる場合、テーブルは連結されません。
[ロードスクリプトの編集]画面を起動します。
「商品分類番号」のコメントアウトを解除し、「売上金額」の項目名を変更します。

concatenate-130.png

リロードしてください。
さきほどと同様、60件のテーブルが二つできています。

concatenate-131.pngconcatenate-132.png

ここまでの内容をまとめておきます。
項目数と項目名がすべて一致しているテーブルがあった場合、QlikViewは同じ構造のテーブルとみなして、テーブルを連結します。
このとき、項目の順序は関係ありません。

テーブルの連結2(項目数が異なるテーブルの連結)

こんどはもう少し複雑なデータを使用してみましょう。
ここでは、売上実績2.xlsを使用します。

売上実績2.xlsでも、2008年と2009年のデータが二つのシートに分かれて格納されていますが、前回よりもデータが多少複雑になっています。
とくに右端の二列(更新日と更新日時)は、2009年の方にしかないという点を認識しておいてください。

concatenate-201.pngconcatenate-202.png

それでは、ドキュメントを作成してみましょう。
まずはファイルを新規に作成し、[ロードスクリプトの編集]画面を起動します。
ロードスクリプトの最下行にカーソルをあわせて、[テーブルファイル]ボタンをクリックします。

concatenate-203.png

「売上実績2.xls」を選択し、「売上2008」シートを取り込んでください。

concatenate-204.pngconcatenate-205.png

さらに、「売上実績2.xls」の「売上2009」シートも取り込んでください。

concatenate-206.pngconcatenate-207.png

前回と同じようにテーブル名を指定してください。

concatenate-208.png

以上でロードスクリプトは完成です。
リロードを実行してください。

concatenate-209.png

ファイルを保存してください。

concatenate-210.png

二つのシートで項目が食い違っているため(更新日と更新日時は売上2009シートにしかない)、テーブルは連結されません。

concatenate-211.pngconcatenate-212.png

テーブルが連結されるようにしてみましょう。
単純に足りない項目を追加し、項目を揃えることでテーブルが連結できます。
つまり、ここでは「売上2008$」テーブルに更新日と更新日時の項目を追加します。

[ロードスクリプトの編集]画面を起動します。
一つ目のLOAD文を以下のように変更します。

変更前

売上金額

変更後

売上金額,
Null() as 更新日,
Null() as 更新日時

「売上金額」の末尾に,(カンマ)を追加するのを忘れないでください。

concatenate-213.png

適当な日付や時刻を指定してもテーブルは連結できますが、データが無いということを明らかにするために、ここではNULL値を指定しています。
QlikViewでNULL値を指定したい場合はNull関数を使用します。QlikViewには「NULL」という定数や、キーワードの類はありません。Null関数は関数であるため末尾の()が必要であることに注意してください。

再度リロードを実行してみましょう。
テーブルが連結されました。

concatenate-214.pngconcatenate-215.png

テーブルボックスを使用して、データを確認してみましょう。
シートの余白を右クリックし[シートオブジェクトの追加]→[テーブルボックス]を選択します。

concatenate-216.png

[すべて追加]ボタンをクリックして、すべての項目を選択します。

concatenate-217.png

[ロード順]ボタンをクリックして、項目の順序を調整します。
[OK]ボタンをクリックします。

concatenate-218.png

テーブルボックスが作成されました。
テーブルは連結されていますが、更新日と更新日時の書式がおかしくなっています。

concatenate-219.png

はじめに取り込んだデータがNULL値であったため、書式がうまく反映されずこのような表示になっています。言い換えると、QlikViewが日付かどうか判断できなかったために、数値で表示されているということです。

ロードスクリプトでデータの型を明示するには、Date関数(日付)やTimestamp関数(日付時刻)を使用します。
LOAD文を以下のように編集します。
変更前

Null() as 更新日,
Null() as 更新日時

変更後

Date(Null()) as 更新日,
Timestamp(Null()) as 更新日時

concatenate-220.png

リロードを実行してください。
下図のように列の横幅が足りない場合は、右クリック→[データに列幅を合わせる]を選択してください。

concatenate-221.pngconcatenate-222.png

日付や日付時刻の書式が設定できました。

concatenate-223.png

追加の設定

静的な値を持つ項目の追加

ここからは、さらに追加の設定をしてみましょう。
あらためてもとのデータを確認すると、シートの中には年のデータがありません。
このファイルでは、年ごとにシートが分かれており、各シートには月日のデータしかありません。

concatenate-224.png

年のデータをロードスクリプトで追加してみましょう。

[ロードスクリプトの編集]画面を起動してください。
まず一つ目のLOAD文に2008年の値を追加します。
以下のように単純に値を指定すれば、静的な値を持つ項目を作成できます。

LOAD 2008 as 年,

concatenate-225.png

文字の値を追加したい場合は、文字列を’(シングルコーテーション)で囲んでください。

'2008年' as 年,

同様にして二つ目のLOAD文に2009年の値を追加します。

LOAD 2009 as 年,

concatenate-226.png

リロードを実行してください。
テーブルボックスのプロパティを開き、[基本設定]タブで「年」の項目を追加します。

concatenate-227.png

[上へ][下へ]ボタンを使用して、「年」の項目を一番上に移動します。
[OK]ボタンをクリックしてください。

concatenate-228.png

テーブルボックスに「年」の項目が追加できました。

concatenate-229.png

テーブルを強制的に連結する

さきほどはNULL値を追加することでテーブルを連結しましたが、「Concatenate」というコマンドを使用することで、構造が違うテーブルを強制的に連結できます。

[ロードスクリプトの編集]画面を起動します。
まず、一つ目のLOAD文をもとの状態に戻します。以下のように変更してください。
変更前

売上金額,
Date(Null()) as 更新日,
Timestamp(Null()) as 更新日時

変更後

売上金額
//Date(Null()) as 更新日,
//Timestamp(Null()) as 更新日時

「売上金額」末尾の,(カンマ)を削除するのを忘れないでください。

concatenate-230.png

二つのLOAD文の間に「Concatenate」というコマンドを指定します。

LOAD ...

Concatenate

LOAD ...

concatenate-231.png

リロードするとテーブルが連結されていることが分かります。
結果はNULL値を手動で追加したときと同じです。

concatenate-232.png

このように足りない項目を手動で追加しなくても、「Concatenate」というコマンドだけで強制的にテーブルを連結できます。

補足説明

データ型をGUIから明示する

さきほどはロードスクリプトの中でデータ型を明示しましたが、ユーザーインターフェース側から指定することもできます。
もとの表示

concatenate-301.png

テーブルボックスのプロパティを開きます。
[数値書式]タブで[ドキュメント初期設定を上書きする]にチェックを入れます。
項目を選択して、書式を設定します。

concatenate-302.pngconcatenate-303.png

書式が設定できました。

concatenate-304.png

オブジェクトのプロパティは、そのオブジェクトのみの設定です。
ドキュメント全体で書式設定をしたい場合は、ドキュメントプロパティから同様の設定をしてください。
[設定]メニューから[ドキュメントプロパティ]を選択します。

concatenate-305.png

[数値書式]タブから同様に設定できます。

concatenate-306.png

テーブルを強制的に分割する

「Concatenate」コマンドを使用することで、構造が異なっていても強制的にテーブルを連結できました。
逆に「NoConcatenate」というコマンドを使用することで、構造が同じでも強制的にテーブルを分割できます。

ロードスクリプトを以下のように編集します。
まず、一つ目のLOAD文の指定をもとに戻します。
二つのLOAD文の間に「NoConcatenate」というコマンドを指定します。

LOAD ...

NoConcatenate

LOAD ...

concatenate-307.png

リロードを実行してください。
テーブルは連結されません。

concatenate-308.pngconcatenate-309.png

「NoConcatenate」の使い道が少し想像しにくいと思いますので、補足しておきます。
まず、QlikViewでは「Resident」というコマンドで読み込み済みのテーブルを再読み込みできます。また「Drop Table」というコマンドでテーブルを削除できます。これらを使用することで、一回のLOAD文ではできない複雑な処理ができます。

たとえば、以下のような処理ができます。
テーブル1を読み込み→テーブル1を再読み込みしてデータを加工し、テーブル2とする→テーブル1は削除する
このような処理の流れのときに、途中でテーブル1とテーブル2が連結されるのを防ぐために、「NoConcatenate」を使用することがあります。

データ型を明示する関数

チュートリアルの中では、Date関数とTimestamp関数を使用してデータ型を明示しました。
データ型を明示する関数には、他にも以下のようなものがあります。

  • Text関数 文字型
  • Num関数 数値型
  • Date関数 日付型
  • Timestamp関数 日付時刻型
  • Time関数 時刻型

振り返り

今回は複数のテーブルを連結する方法についてご覧いただきました。以下に重要なポイントをまとめます。

・基本動作
・テーブルの構造が同じ場合、自動的にテーブルが一つに連結される。
・テーブルが連結されるかどうかは、項目数と項目名が一致しているかどうかによって判断される。項目の順序は関係ない。
・ConcatenateとNoConcatenate
・「Concatenate」コマンドで、構造が異なるテーブルを強制的に連結できる。
・「NoConcatenate」コマンドで、構造が同じテーブルを強制的に分割できる。
・項目の追加
・NULL値の項目を追加するには、Null関数を使う。

Null() as 項目名

・静的な値を持つ項目を追加するには、以下のように指定する。

数値 as 項目名
'文字列' as 項目名

・データ型の明示
テーブルを連結した際、データ型がうまく認識されない場合は、手動で設定する。
・ロードスクリプト側
Date関数(日付型)、Timestamp関数(日付時刻型)などを指定する。

Date(Null()) as 項目名

・ユーザーインターフェース側
・ドキュメント全体の設定
[設定]→[ドキュメントプロパティ]→[数値書式]タブ
・オブジェクトごとの設定
オブジェクトのプロパティ→[数値書式]タブ
[ドキュメント初期設定を上書きする]にチェックを入れてから書式を設定する。

最後に

今回は複数のExcelファイルを一つにまとめる例を紹介しましたが、この方法だとファイルの数だけLOAD文を指定しなければならないため、ファイルが大量にある場合は少し手間です。

QlikViewのロードスクリプトでは繰り返し処理(FOR文)が使えるため、これを使えば複数のファイルを一括で読み込むこともできます。
たとえば、あるディレクトリ以下にあるExcelファイルをすべて読み込むといった指定ができます。
この方法については、次回説明いたします。

お疲れ様でした。

関連している記事

  • データ準備
2021.04.08

Excel ピボットテーブル データの取り込み

Excel ピボットテーブル データの取り込み

  • データ準備
2021.03.22

データマネージャーでの簡単操作で取り込んだ複数テーブルを縦に連結する方法

データマネージャーでの簡単操作で取り込んだ複数テーブルを縦に連結する方法

  • データ準備
2021.03.15

データマネージャーでの「合成キー」作成方法

データマネージャーでの「合成キー」作成方法

ページの先頭へ戻る