マスタに合致する/合致しないデータのみ取り込む
Qlikにデータを取り込むとき、「使わないデータも含まれていそうだったが、データを細かく確認するのは面倒だし、とりあえず全部取り込んだ」という経験はありませんか？
不要なデータが多いと分析しにくいですし、データ量によってはロードに長時間かかったりしてしまうこともあります。できれば取り込みたくないですよね。
“不要なデータ”が何かは人によって異なると思いますが、今回は一例として、マスタに合致しないデータを“不要なデータ”と仮定しましょう。
つまり、マスタに合致するデータのみ取り込みたい！という状況ですね。
マスタに合致するデータのみ取り込むにはどうすればいいのか、ご紹介します。
…せっかくなので、マスタに合致しないデータのみ取り込むパターンもご紹介しますね。
目次
※本記事は「2023年06月時点のSaaS」で作成しています。
どんな時に使う？
マスタに合致するデータのみ、もしくはマスタに合致しないデータのみ取り込みたいのは、どんな時でしょうか。
イメージしやすいよう、はじめにデータ例をお見せしますね。
『売上明細』テーブルに、『商品マスタ』テーブルの「商品コード」に合致するデータと、合致しないデータがあるとします。
▼商品マスタ
商品コード | 価格 |
|---|---|
A1 | 100 |
A2 | 150 |
B1 | 200 |
B2 | 250 |
C1 | 300 |
C2 | 350 |
D1 | 400 |
D2 | 450 |
▼売上明細
売上日 | 商品コード |
|---|---|
2023/01/25 | A1 |
2023/01/25 | B1 |
2023/02/22 | A1 |
2023/02/22 | C1 |
2023/02/22 | X3 |
2023/04/25 | A2 |
2023/04/25 | A5 |
2023/04/25 | D2 |
マスタに合致するデータのみ、もしくはマスタに合致しないデータのみ取り込みたい状況としては、たとえば以下のとおりです。
●マスタに合致するデータのみ、取り込みたい状況の一例
『売上明細』のデータ範囲が広く、異なるマスタの商品も含まれている状況。
今回必要なのは『商品マスタ』にあるデータのみ。それ以外のデータは不要。
●マスタに合致しないデータのみ、取り込みたい状況の一例
担当者の手入力等により、マスタに合致しない誤ったデータが存在する状況。
誤ったデータのみ『売上明細』から抜き出し確認して、全データがマスタと合致するよう、『売上明細』を修正したい。
上記のような状況であれば、これから紹介する方法が役に立つはずです。
それでは、具体的な内容を見ていきましょう。
マスタに合致するデータのみ、取り込む方法
以下のようなスクリプトを書きます。
記述例
[商品マスタ]:
LOAD
商品コード,
価格
FROM [lib://DataFiles/商品マスタ.xlsx]
(ooxml, embedded labels, table is Sheet1);
[売上明細]:
LOAD
売上日,
商品コード
FROM [lib://DataFiles/売上明細.xlsx]
(ooxml, embedded labels, table is Sheet1)
Where Exists(商品コード);
＜解説＞
・Where句で条件を指定して、取り込むデータを制御します。
・Exists関数で、取り込み済みの値かどうかを判定します。
Where Exists(商品コード)と指定することで、『商品マスタ』で取り込み済みの「商品コード」のデータのみ、取り込むことができます。
▼売上明細（取り込んだ後）
売上日 | 商品コード |
|---|---|
2023/01/25 | A1 |
2023/01/25 | B1 |
2023/02/22 | A1 |
2023/02/22 | C1 |
2023/04/25 | A2 |
2023/04/25 | D2 |
マスタに合致しないデータのみ、取り込む方法
以下のようなスクリプトを書きます。
記述例
[商品マスタ]:
LOAD
商品コード,
価格
FROM [lib://DataFiles/商品マスタ.xlsx]
(ooxml, embedded labels, table is Sheet1);
[売上明細]:
LOAD
売上日,
商品コード
FROM [lib://DataFiles/売上明細.xlsx]
(ooxml, embedded labels, table is Sheet1)
Where not Exists(商品コード);
＜解説＞
【マスタに合致するデータのみ、取り込む方法】とほぼ同様ですが、Where句にnotを加えている点が異なります。
Where not Exists(商品コード)と指定することで、『商品マスタ』で取り込んでいない「商品コード」のデータのみ、取り込むことができます。
▼売上明細（取り込んだ後）
売上日 | 商品コード |
|---|---|
2023/02/22 | X3 |
2023/04/25 | A5 |
併せて使おう！便利な関数
前述2つの方法のどちらに対しても使える便利な関数として、RecNo関数があります。
どんな点が便利なのか、【マスタに合致しないデータのみ、取り込む方法】を例として以下に説明しますので、併せて使ってみてください。
■ここが便利！
元ファイルの何行目に対象のデータがあるか、一目でわかる！
■こんな時に便利！
マスタに合致しないデータは誤りであり『売上明細』内のデータを修正したい、という状況であれば、修正対象のデータが何行目にあるのかも知っておきたいところです。
そんな時は【マスタに合致しないデータのみ、取り込む方法】に少し記述を加えると、「行番号」も確認することができるようになります！
以下のようなスクリプトを書きます。
記述例
//[商品マスタ]は変更点がないため割愛します。
[売上明細]:
LOAD
売上日,
商品コード,
RecNo()+1 as 行番号
FROM [lib://DataFiles/売上明細.xlsx]
(ooxml, embedded labels, table is Sheet1)
Where not Exists(商品コード);
＜解説＞
RecNo関数は、取り込み元のテーブル上の行番号を取得します。
今回使用したデータはExcelファイルであり、以下画面キャプチャのとおり、項目名が1行目としてカウントされているため、RecNo()に+1も加えることで行数が一致します。
▼Excelファイルの画面キャプチャ（取り込み元のファイル）
▼売上明細（取り込んだ後）
売上日 | 商品コード | 行番号 |
|---|---|---|
2023/02/22 | X3 | 6 |
2023/04/25 | A5 | 8 |
さいごに
今回紹介したExists関数の詳細については、以下のメーカーヘルプをご覧ください。
Exists - スクリプト関数
また、Exists関数はQlik Senseだけでなく、QlikViewでも利用可能です。
※本記事のスクリプト例は、Qlik Sense SaaSの記述形式です。
Qlik Sense EnterpriseやQlikViewにコピーペーストして流用される場合は、FROMの形式を変更してください。
なお、Where句、RecNo関数については、本ブログで過去に詳しく解説しています。
以下が対象の記事ですので、ぜひご覧ください。