join プレフィックスは、ロード済みのテーブルを名前が付いた既存テーブルまたは直前に作成されたデータ テーブルと結合します。
データを結合すると、追加の項目または属性のセット (ターゲット テーブルにまだ存在しないもの) によってターゲット テーブルが拡張されます。ソース データ セットとターゲット テーブルの間の共通の項目名は、新しい入力レコードを関連付ける方法を決定するために使用されます。これは一般に「自然結合」と呼ばれます。Qlik 結合操作では、結合の関連付けの一意性と使用される結合の種類に応じて、結果のターゲット テーブルのレコードが開始時よりも増減する可能性があります。
結合のタイプ
結合には次の 4 つのタイプがあります。
-
左結合
-
内部結合
-
外部結合
-
右結合
さまざまなタイプの結合操作からの結果セットの例
左結合
左結合は、最も一般的な結合タイプです。たとえば、トランザクション データ セットがあり、それを参照データ セットと組み合わせたい場合、通常は Left Join を使用します。最初にトランザクション テーブルをロードし、次に Left Join プレフィックスを介して既にロードされているトランザクション テーブルに結合しながら、参照データ セットをロードします。Left Join は、すべてのトランザクションをそのまま保持し、一致が見つかった補足参照データ項目を追加します。
左結合
内部結合
一致する関連付けがある結果のみを対象とする 2 つのデータ セットがある場合は、Inner Joinの使用を検討してください。これにより、一致するものが見つからない場合、ロードされたソース データとターゲット テーブルの両方からすべてのレコードが削除されます。その結果、結合操作が行われる前よりもターゲット テーブルのレコードが減少する可能性があります。
内部結合
外部結合 (既定)
ターゲット レコードとすべての着信レコードの両方を保持する必要がある場合は、Outer Joinを使用します。一致が見つからない場合、結合の反対側の項目は未入力 (null) のままですが、レコードの各セットは引き続き保持されます。
type キーワードを省略した場合、既定の結合タイプは外部結合となります。
外部結合
右結合
この結合タイプは、ロードされるすべてのレコードを保持しながら、結合の対象となるテーブル内のレコードを、着信レコードに関連付けの一致があるレコードのみに減らします。これはニッチな結合タイプであり、事前にロードされたレコードのテーブルを必要なサブセットにトリミングする手段として使用されることがあります。
右結合
クロス結合
「クロス結合」操作による結果セットの例
構文と引数
構文:
[inner | outer | left | right ]Join [ (tablename ) ]( loadstatement | selectstatement )
| 引数 | 説明 |
|---|---|
| tablename | 名前が付いたテーブルが、ロード済みのテーブルと比較されます。 |
| loadstatementまたはselectstatement | ロード済みテーブルの LOAD または SELECT ステートメントです。 |
関連トピック
これらのトピックは、この関数を使用するのに役立つかもしれません。
| トピック | 説明 |
|---|---|
| Join と Keep を使用したテーブルの結合 | このトピックでは、データ セットの「結合」と「保持」の概念について詳しく説明します。 |
| Keep | Keep ロード プレフィックスは Join プレフィックスに似ていますが、ソース データセットとターゲット データセットを結合しません。代わりに、採用された操作のタイプ (内側、外側、左、または右) に従って各データセットをトリムします。 |
ロード スクリプトの例 - 左結合
左結合では、メイン (ターゲット) テーブルのすべてのレコードを保持し、2 番目の (ソース) テーブルから一致するデータを追加します。ソースに一致するデータがない場合でも、メイン テーブルのレコードは引き続き表示されますが、ソース テーブルからの項目は空になります。
アプリケーションにスクリプト例を追加して実行します。結果を表示するには、結果列に含まれている項目をアプリケーションのシートに追加します。
ロード スクリプト
Table1:
Load * inline [
Column1, Column2
A, B
1, aa
2, cc
3, ee ];
Table2:
Left Join Load * inline [
Column1, Column3
A, C
1, xx
4, yy ]; インライン ロードの使用の詳細については、「インライン ロードを使用したデータのロード」を参照してください。
結果
| Column1 | Column2 | Column3 |
|---|---|---|
| A | B | C |
| 1 | aa | xx |
| 2 | cc | - |
| 3 | ee | - |
この例は、最初の (左) テーブルに存在する値のみが結合される左結合出力を示しています。
概要
注文データのリストがあるものの、すべての注文に対応する顧客レコードが存在するわけではない、という状況を想定します。
ロード スクリプト
Orders:
Load * inline [
OrderID, CustomerID, Amount
1, 101, 200
2, 102, 150
3, 104, 400
];
Customers:
Left Join (Orders)
Load * inline [
CustomerID, Name
101, Alice
102, Bob
103, Carol
];結果
データ モデル ビューアでプレビュー ウィンドウを展開すると、完全な結果セットの一部がテーブル形式で表示されます。
|
OrderID |
CustomerID | Amount | 名前 |
|---|---|---|---|
|
1 |
101 | 200 | Alice |
|
2 |
102 | 150 | Bob |
|
3 |
104 | 400 | - |
OrderID 1 と 2 には一致する顧客がいます。OrderID 3 には一致する顧客がいないため、 [名前] は空白です。
概要
データ ロード エディターを開き、以下のロード スクリプトを新しいセクションに追加します。
ロード スクリプトには次が含まれています:
-
Changesという名前のテーブルに読み込まれる、変更レコードを表すデータセット。これには、ステータス ID キー項目が含まれます。
-
変更ステータスを表す 2 番目のデータセット。ロードされ、左の Join ロード プレフィックスで結合することによって元の変更レコードと結合されます。
この左結合により、共通のステータス ID に基づいて着信ステータス レコードの一致が見つかったステータス属性を追加しながら、変更レコードを確実にそのまま残すことができます。
ロード スクリプト
Changes:
Load * inline [
Change ID Status ID Scheduled Start Date Scheduled End Date Business Impact
10030 4 19/01/2022 23/02/2022 None
10015 3 04/01/2022 15/02/2022 Low
10103 1 02/04/2022 29/05/2022 Medium
10185 2 23/06/2022 08/09/2022 None
10323 1 08/11/2022 26/11/2022 High
10326 2 11/11/2022 05/12/2022 None
10138 2 07/05/2022 03/08/2022 None
10031 3 20/01/2022 25/03/2022 Low
10040 1 29/01/2022 22/04/2022 None
10134 1 03/05/2022 08/07/2022 Low
10334 2 19/11/2022 06/02/2023 Low
10220 2 28/07/2022 06/09/2022 None
10264 1 10/09/2022 17/10/2022 Medium
10116 1 15/04/2022 24/04/2022 None
10187 2 25/06/2022 24/08/2022 Low
] (delimiter is '\t');
Status:
Left Join (Changes)
Load * inline [
Status ID Status Sub Status
1 Open Not Started
2 Open Started
3 Closed Completed
4 Closed Cancelled
5 Closed Obsolete
] (delimiter is '\t');結果
データ モデル ビューアを開き、データ モデルの形状を確認します。非正規化されたテーブルが 1 つだけ存在します。これは、元のすべての変更レコードの組み合わせであり、一致するステータス属性が各変更レコードに結合されています。
|
変更 |
|---|
|
変更 ID |
|
ステータスID |
|
開始予定日付 |
|
終了予定日付 |
|
ビジネスへの影響 |
|
ステータス |
|
サブ ステータス |
データ モデル ビューアでプレビュー ウィンドウを展開すると、この完全な結果セットの一部がテーブルに編成されて表示されます。
|
変更 ID |
ステータスID | 開始予定日付 | 終了予定日付 | ビジネスへの影響 | ステータス | サブ ステータス |
|---|---|---|---|---|---|---|
|
10030 |
4 | 19/01/2022 | 23/02/2022 | なし | 終了 | キャンセル済み |
|
10031 |
3 | 20/01/2022 | 25/03/2022 | 低 | 終了 | 完了 |
|
10015 |
3 | 04/01/2022 | 15/02/2022 | 低 | 終了 | 完了 |
|
10103 |
1 | 02/04/2022 | 29/05/2022 | 中間 | 開く | 開始されていない |
| 10116 | 1 | 15/04/2022 | 24/04/2022 | なし | 開く | 開始されていない |
|
10134 |
1 | 03/05/2022 | 08/07/2022 | 低 | 開く | 開始されていない |
| 10264 | 1 | 10/09/2022 | 17/10/2022 | 中 | 開く | 開始されていない |
|
10040 |
1 | 29/01/2022 | 22/04/2022 | なし | 開く | 開始されていない |
| 10323 | 1 | 08/11/2022 | 26/11/2022 | 高 | 開く | 開始されていない |
| 10187 | 2 | 25/06/2022 | 24/08/2022 | 低 | 開く | 開始済み |
| 10185 | 2 | 23/06/2022 | 08/09/2022 | なし | 開く | 開始済み |
| 10220 | 2 | 28/07/2022 | 06/09/2022 | なし | 開く | 開始済み |
| 10326 | 2 | 11/11/2022 | 05/12/2022 | なし | 開く | 開始済み |
|
10138 |
2 | 07/05/2022 | 03/08/2022 | なし | 開く | 開始済み |
| 10334 | 2 | 19/11/2022 | 06/02/2023 | 低 | 開く | 開始済み |
[ステータス] テーブルの 5 行目 (ステータス ID:「5」、ステータス:「Closed」、サブステータス:「Obsolete」) は [変更] テーブルのどのレコードにも対応していないため、この行の情報は上記の結果セットには表示されません。
データ ロード エディタに戻ります。データをロードしてシートを開きます。新しいテーブルを作成し、この項目を軸として追加します: Status。
このメジャーを追加します。
=Count([Change ID])
これで、ステータスごとに変更の数を調べることができます。
| ステータス | =Count([Change ID]) |
|---|---|
| 開く | 12 |
| 終了 | 3 |
ロード スクリプトの例 - 内部結合
内部結合は、両方のテーブルに存在するレコードのみを保持します (一致する項目値に基づく)。
ロード スクリプト
アプリケーションにスクリプト例を追加して実行します。結果を表示するには、結果列に含まれている項目をアプリケーションのシートに追加します。
Table1:
Load * inline [
Column1, Column2
A, B
1, aa
2, cc
3, ee ];
Table2:
Inner Join Load * inline [
Column1, Column3
A, C
1, xx
4, yy ];インライン ロードの使用の詳細については、「インライン ロードを使用したデータのロード」を参照してください。
結果
| Column1 | Column2 | Column3 |
|---|---|---|
| A | B | C |
| 1 | aa | xx |
この例は、最初の (左) テーブルと 2 番目の (右) テーブルの両方に存在する値のみが結合される内部結合出力を示しています。
概要
注文と、出荷済みの注文のリストがあります。出荷済みの注文のみを必要としています。
ロード スクリプト
Orders:
Load * inline [
OrderID, Amount
1, 200
2, 150
3, 400
];
Shipped:
Inner Join (Orders)
Load * inline [
OrderID, ShipDate
2, 2022-05-01
3, 2022-05-02
4, 2022-05-03
];結果
データ モデル ビューアでプレビュー ウィンドウを展開すると、完全な結果セットの一部がテーブル形式で表示されます。
| OrderID | Amount | ShipDate |
|---|---|---|
| 2 | 150 | 2022-05-01 |
| 3 | 400 | 2022-05-02 |
両方のテーブルに存在するため、OrderID 2 と 3 のみが表示されます。
概要
データ ロード エディターを開き、以下のロード スクリプトを新しいセクションに追加します。
ロード スクリプトには次が含まれています:
-
Changesという名前のテーブルに読み込まれる、変更レコードを表すデータセット。
-
ソース システム JIRA から派生した変更レコードを表す 2 番目のデータセット。変更ステータスを表す 2 番目のデータセット。ロードされ、左の Inner Join ロード プレフィックスで結合することによって元の変更レコードと結合されます。
この Inner Join により、両方のデータセットで見つかった 5 つの変更レコードのみが確実に保持されます。
ロード スクリプト
Changes:
Load * inline [
Change ID Status ID Scheduled Start Date Scheduled End Date Business Impact
10030 4 19/01/2022 23/02/2022 None
10015 3 04/01/2022 15/02/2022 Low
10103 1 02/04/2022 29/05/2022 Medium
10185 2 23/06/2022 08/09/2022 None
10323 1 08/11/2022 26/11/2022 High
10326 2 11/11/2022 05/12/2022 None
10138 2 07/05/2022 03/08/2022 None
10031 3 20/01/2022 25/03/2022 Low
10040 1 29/01/2022 22/04/2022 None
10134 1 03/05/2022 08/07/2022 Low
10334 2 19/11/2022 06/02/2023 Low
10220 2 28/07/2022 06/09/2022 None
10264 1 10/09/2022 17/10/2022 Medium
10116 1 15/04/2022 24/04/2022 None
10187 2 25/06/2022 24/08/2022 Low
] (delimiter is '\t');
JIRA_changes:
Inner Join (Changes)
Load
[Ticket ID] AS [Change ID],
[Source System]
inline
[
Ticket ID Source System
10000 JIRA
10030 JIRA
10323 JIRA
10134 JIRA
10334 JIRA
10220 JIRA
20000 TFS
] (delimiter is '\t');結果
データをロードしてシートを開きます。新しいテーブルを作成し、これらの項目を軸として追加します:
-
Source System
-
Change ID
-
Business Impact
これで、結果の 5 つのレコードを調べることができます。Inner Join からの結果テーブルには、両方のデータセットで一致する情報を持つレコードのみが含まれます。
| ソース システム | 変更 ID | ビジネスへの影響 |
|---|---|---|
| JIRA | 10030 | なし |
| JIRA | 10134 | 低 |
| JIRA | 10220 | なし |
| JIRA | 10323 | 高 |
| JIRA | 10334 | 低 |
ロード スクリプトの例 - 外部結合
外部結合は、両方のテーブルからすべてのレコードを保持します。一致しない場合、項目は空白のままになります。
ロード スクリプト
アプリケーションにスクリプト例を追加して実行します。結果を表示するには、結果列に含まれている項目をアプリケーションのシートに追加します。
Table1:
Load * inline [
Column1, Column2
A, B
1, aa
2, cc
3, ee ];
Table2:
Outer Join Load * inline [
Column1, Column3
A, C
1, xx
4, yy ]; インライン ロードの使用の詳細については、「インライン ロードを使用したデータのロード」を参照してください。
結果
| Column1 | Column2 | Column3 |
|---|---|---|
| A | B | C |
| 1 | aa | xx |
| 2 | cc | - |
| 3 | ee | - |
| 4 | - | yy |
この例では、2 つのテーブル Table1 と Table2 が、Table1 というラベルの付いた単一のテーブルにマージされています。このような場合、外部プレフィックスは、複数のテーブルを 1 つのテーブルに結合して、単一のテーブルの値に対して集計を実行するためによく使用されます。
概要
一部の顧客に注文がない場合や、一部の注文に顧客レコードがない場合でも、すべての顧客と注文の完全なリストを必要とします。
ロード スクリプト
Orders:
Load * inline [
OrderID, CustomerID
1, 101
2, 102
3, 104
];
Customers:
Outer Join (Orders)
Load * inline [
CustomerID, Name
101, Alice
102, Bob
103, Carol
];結果
データ モデル ビューアでプレビュー ウィンドウを展開すると、この完全な結果セットの一部がテーブルに編成されて表示されます。
| OrderID | CustomerID | Name |
|---|---|---|
| 1 | 101 | Alice |
| 2 | 102 | Bob |
| 3 | 104 | - |
| - | 103 | Carol |
OrderID 3 には一致する顧客がありません (名前 は空白)。
CustomerID 103 (Carol) には一致する注文がありません (OrderID は空白)。
概要
データ ロード エディターを開き、以下のロード スクリプトを新しいセクションに追加します。
ロード スクリプトには次が含まれています:
-
Changesという名前のテーブルに読み込まれる、変更レコードを表すデータセット。
-
ソース システム JIRA から派生した変更レコードを表す 2 番目のデータセット。変更ステータスを表す 2 番目のデータセット。これは、ロードされ、左の Outer Join ロード プレフィックスで結合することによって元の変更レコードと結合されます。
これにより、両方のデータセットから重複するすべての変更レコードが確実に保持されます。
ロード スクリプト
// 8 Change records
Changes:
Load * inline [
Change ID Status ID Scheduled Start Date Scheduled End Date Business Impact
10030 4 19/01/2022 23/02/2022 None
10015 3 04/01/2022 15/02/2022 Low
10138 2 07/05/2022 03/08/2022 None
10031 3 20/01/2022 25/03/2022 Low
10040 1 29/01/2022 22/04/2022 None
10134 1 03/05/2022 08/07/2022 Low
10334 2 19/11/2022 06/02/2023 Low
10220 2 28/07/2022 06/09/2022 None
] (delimiter is '\t');
// 6 Change records
JIRA_changes:
Outer Join (Changes)
Load
[Ticket ID] AS [Change ID],
[Source System]
inline
[
Ticket ID Source System
10030 JIRA
10323 JIRA
10134 JIRA
10334 JIRA
10220 JIRA
10597 JIRA
] (delimiter is '\t');結果
データをロードしてシートを開きます。新しいテーブルを作成し、これらの項目を軸として追加します:
-
Source System
-
Change ID
-
Business Impact
これで、結果の 10 つのレコードを調べることができます。
| ソース システム | 変更 ID | ビジネスへの影響 |
|---|---|---|
| JIRA | 10030 | なし |
| JIRA | 10134 | 低 |
| JIRA | 10220 | なし |
| JIRA | 10323 | - |
| JIRA | 10334 | 低 |
| JIRA | 10597 | - |
| - | 10015 | 低 |
| - | 10031 | 低 |
| - | 10040 | なし |
| - | 10138 | なし |
ロード スクリプトの例 - 右結合
右結合では、ソース テーブルのすべてのレコードが保持され、ターゲット テーブルから一致する情報が追加されます。ターゲット テーブルに一致するものがない場合、その項目は空白になります。
ロード スクリプト
アプリケーションにスクリプト例を追加して実行します。結果を表示するには、結果列に含まれている項目をアプリケーションのシートに追加します。
Table1:
Load * inline [
Column1, Column2
A, B
1, aa
2, cc
3, ee ];
Table2:
Right Join Load * inline [
Column1, Column3
A, C
1, xx
4, yy ];インライン ロードの使用の詳細については、「インライン ロードを使用したデータのロード」を参照してください。
結果
| Column1 | Column2 | Column3 |
|---|---|---|
| A | B | C |
| 1 | aa | xx |
| 4 | - | yy |
この例は、2 番目の (右) テーブルに存在する値のみが結合される右結合出力を示しています。
概要
ソース テーブルは顧客のリストです。すべての顧客と、一致する注文をすべて取得します。
ロード スクリプト
Orders:
Load * inline [
OrderID, CustomerID
1, 101
2, 102
3, 104
];
Customers:
Right Join (Orders)
Load * inline [
CustomerID, Name
101, Alice
102, Bob
103, Carol
];結果
データ モデル ビューアでプレビュー ウィンドウを展開すると、この完全な結果セットの一部がテーブルに編成されて表示されます。
| CustomerID | Name | OrderID |
|---|---|---|
| 101 | Alice | 1 |
| 102 | Bob | 2 |
| 103 | Carol | - |
すべての顧客が表示されます。Carol には注文がありません (OrderID は空白)。
概要
データ ロード エディターを開き、以下のロード スクリプトを新しいセクションに追加します。
ロード スクリプトには次が含まれています:
-
Changesという名前のテーブルに読み込まれる、変更レコードを表すデータセット。
-
Teamwork のソース システムからの変更レコードを表す 2 番目のデータセット。これはロードされ、Right Join ロード プレフィックスで結合することによって元のレコードと結合されます。
これにより、 Teamwork 変更レコードのみが保持され、ターゲット テーブルに一致する Change IDがなくても Teamwork レコードが失われることはありません。
ロード スクリプト
Changes:
Load * inline [
Change ID Status ID Scheduled Start Date Scheduled End Date Business Impact
10030 4 19/01/2022 23/02/2022 None
10015 3 04/01/2022 15/02/2022 Low
10103 1 02/04/2022 29/05/2022 Medium
10185 2 23/06/2022 08/09/2022 None
10323 1 08/11/2022 26/11/2022 High
10326 2 11/11/2022 05/12/2022 None
10138 2 07/05/2022 03/08/2022 None
10031 3 20/01/2022 25/03/2022 Low
10040 1 29/01/2022 22/04/2022 None
10134 1 03/05/2022 08/07/2022 Low
10334 2 19/11/2022 06/02/2023 Low
10220 2 28/07/2022 06/09/2022 None
10264 1 10/09/2022 17/10/2022 Medium
10116 1 15/04/2022 24/04/2022 None
10187 2 25/06/2022 24/08/2022 Low
] (delimiter is '\t');
Teamwork_changes:
Right Join (Changes)
Load
[Ticket ID] AS [Change ID],
[Source System]
inline
[
Ticket ID Source System
10040 Teamwork
10015 Teamwork
10103 Teamwork
10031 Teamwork
50231 Teamwork
] (delimiter is '\t');結果
データをロードしてシートを開きます。新しいテーブルを作成し、これらの項目を軸として追加します:
-
Source System
-
Change ID
-
Business Impact
これで、結果の 5 つのレコードを調べることができます。
| ソース システム | 変更 ID | ビジネスへの影響 |
|---|---|---|
| チームワーク | 10015 | 低 |
| チームワーク | 10031 | 低 |
| チームワーク | 10040 | なし |
| チームワーク | 10103 | 中 |
| チームワーク | 50231 | - |
トラブルシューティング - クロス結合
概要
一致する項目名がない 2 つのテーブルを結合すると、最初のテーブルのすべての行が 2 番目のテーブルのすべての行と結合されます。これにより、レコード数が非常に多くなる可能性 (直積) があり、予期しないデータの関連付けが発生する場合があります。
ロード スクリプト
TableA:
Load * inline [
A_ID, ValueA
1, foo
2, bar
];
TableB:
Join (TableA)
Load * inline [
B_ID, ValueB
10, baz
11, qux
];結果
データ モデル ビューアでプレビュー ウィンドウを展開すると、この完全な結果セットの一部がテーブルに編成されて表示されます。
| A_ID | ValueA | B_ID | ValueB |
|---|---|---|---|
| 1 | foo | 10 | baz |
| 1 | foo | 11 | qux |
| 2 | bar | 10 | baz |
| 2 | bar | 11 | qux |
各 A_ID は各 B_ID とペアになります (合計 4 行)。