レコード間関数の使用:Peek、Previous、および Exists

これらの関数は、現在のレコードの評価に以前にロードされたデータのレコード値が必要な場合に使用します。

チュートリアルのここでは、Peek()、Previous()、および Exists() 関数を検証します。

Peek()

Peek() は、すでにロードされている行に対してテーブルの項目値を返します。行番号は、テーブルと同様に指定できます。行番号が指定されていない場合は、最後にロードされたレコードが使用されます。

構文:  

row は整数にする必要があります。0 は最初のレコード、1 は 2 番目のレコードを示し、以下同様に表されます。負の数は、テーブルの最後から見た順序を表します。 -1 は、読み取られた最後のレコードを示します。

row が指定されていない場合は、-1 として処理されます。

Tablename は、末尾にコロンが付いていない、テーブルのラベルです。tablename が指定されていない場合は、現在のテーブルとして処理されます。LOAD ステートメント以外で使用する、または他のテーブルを参照する場合は、tablename が含まれている必要があります。

Previous()

Previous()は、where節のために破棄されなかった以前の入力レコードのデータを使用して、expr数式の値を算出します。内部テーブルの最初のレコードの場合は、NULL を返します。

構文:  

Previous() 関数をネストすることで、さらに前のレコードにアクセスすることができます。データは入力ソースから直接取得されるため、Qlik Sense にまだロードされていない (つまり、関連するデータベースに保存されていなくても) 項目を参照することもできます。

Exists()

Exists() は、特定の項目値がデータ ロード スクリプトの項目にすでにロードされているかどうかを決定します。この関数は TRUE または FALSE を返すため、LOAD ステートメントまたは IF ステートメントの where 句で使用できます。

構文:  

項目は、スクリプトによってこれまでにロードされたデータの中になければなりません。Expression は、指定した項目において、検索対象項目値として評価される数式です。省略されると、指定された項目の現在のレコード値が使用されます。

Peek() および Previous() の使用

もっとも簡単な形式では、Peek() および Previous() はテーブル内で特定の値を指定するために使用されます。この演習でロードする Employees テーブル内のサンプル データを示します。

現在、これは月、採用、退職などのデータのみを集計しており、Peek() と Previous() 関数を使ってこれに Employee Count と Employee Var の項目を追加して、従業員総数の月ごとの変化を確認します。

次の手順を実行します。

1. 高度なスクリプト チュートリアル アプリを開きます。
2. データ ロード エディターで新しいスクリプト セクションを追加します。
3. このセクションを Employees と呼びます。

4. 右のメニューの [DataFiles] で、[データを選択] をクリックします。

5. Employees.xlsx をアップロードして選択します。
情報メモ[Field names] で [Embedded field names] が選択されていることを確認します。このオプションが選択されていると、データをロードする際にテーブルの項目名が含まれます。
6. [Select data from] (データの選択元) ウィンドウで、[スクリプトを挿入] をクリックします。

これで、スクリプトは次のようになります。

LOAD "Date", Hired, Terminated FROM [lib://DataFiles/Employees.xlsx] (ooxml, embedded labels, table is Sheet1);

7. スクリプトを、次のように修正します。

   [Employees Init]:
   LOAD
       rowno() as Row,
       Date(Date) as Date,
       Hired,
       Terminated,
       If(rowno()=1, Hired-Terminated,  peek([Employee Count], -1)+(Hired-Terminated)) as [Employee Count]
   FROM [lib://DataFiles/Employees.xlsx]		
   (ooxml, embedded labels, table is Sheet1);

Excel シートの Date 項目の日付は、MM/DD/YYYY の書式です。日付がこの書式を使用してシステム変数から正しく解釈されていることを確認するには、Date 関数を Date 項目に適用します。

Peek() 関数が、定義済みの項目にロードされた値を特定します。この数式では、最初に rowno() が 1 と等しいかどうかを確認します。1 と等しい場合、Employee Count は存在しないため、 Hired - Terminated の差をその項目に入力します。

rowno() が 1 より大きい場合は、前月の Employee Count を確認し、その数値をその月の Hired - Terminated の値に加算します。

Peek() 関数に (-1) を使用していることに注意してください。これは、Qlik Sense に現在のレコードを上回るレコードを確認するよう指示しています。(-1) が指定されていない場合は、Qlik Sense は前のレコードを確認することを意味します。

8. 次の内容をスクリプトの最後に追加します。

[Employee Count]:
LOAD
	Row,
	Date,
	Hired,
	Terminated,
	[Employee Count],
	If(rowno()=1,0,[Employee Count]-Previous([Employee Count])) as [Employee Var]
Resident [Employees Init] Order By Row asc;
Drop Table [Employees Init];

Previous() 関数が、定義済みの項目にロードされた最新の値を特定します。この数式では、最初に rowno() が 1 と等しいかどうかを確認します。1 と等しい場合は、前月の Employee Count のレコードが存在せず、それゆえ Employee Var は存在しないため、値として単に 0 を入力します。

rowno()が 1 より大きい場合、Employee Varが存在していることを示すので、先月の Employee Count を確認し、その数値を現在の月の Employee Count から差し引いて Employee Var 項目の値を作成します。

これで、スクリプトは次のようになります。

[Employees Init]:
LOAD
    rowno() as Row,
    Date(Date) as Date,
    Hired,
    Terminated,
    If(rowno()=1, Hired-Terminated,  peek([Employee Count], -1)+(Hired-Terminated)) as [Employee Count]
FROM [lib://DataFiles/Employees.xlsx]
(ooxml, embedded labels, table is Sheet1);

[Employee Count]:
LOAD
    Row,
    Date,
    Hired,
    Terminated,
    [Employee Count],
    If(rowno()=1,0,[Employee Count]-Previous([Employee Count])) as [Employee Var]
Resident [Employees Init] Order By Row asc;	
Drop Table [Employees Init];

9. [データのロード] をクリックします。

アプリ概要の新しいシートで、テーブルの列として Date, Hired, Terminated, Employee Count および Employee Var を使用してテーブルを作成します。 この結果、テーブルは次のようになります。

Peek() と Previous() を使用すると、テーブル内の定義済み行を対象にすることができます。この 2 つの関数の最大の違いは、Peek() 関数ではユーザーがスクリプトにあらかじめロードされていない項目を参照することができますが Previous() 関数はロード済みの項目しか参照できない点です。Previous() は、LOAD ステートメントへの入力で値を返し、Peek() は LOAD ステートメントの出力で値を返します。(RecNo() と RowNo() の違いと同じ。) これは、Where 節を使用すると、2 つの関数が異なる動作をすることを意味します。

Previous() 関数は、ユーザーが現在の値と前の値を表示する必要がある場合に、より適しています。例では、月によって異なる従業員の分散状況を計算しています。

Peek() 関数は、ユーザーがテーブルに事前にロードされていない項目を対象とする場合、または特定の行を対象とする必要がある場合のいずれにも、より適しています。これは、前月の Employee Count をピークすることで Employee Count を計算し、今月の雇用者と退職者数の差異を加算している例で示しています。Employee Count は、オリジナル ファイルの項目ではないことに注意が必要です。

Exists() の使用

Exists() 関数は、スクリプトにおいて Where 節とともに使用され、データ モデルにすでにロードされている関連データがある場合にデータをロードします。

次の例では、Dual() 関数を使用して文字列に数値を割り当てています。

次の手順を実行します。

1. 新しいアプリを作成し、名前を付けます。
2. データ ロード エディターで新しいスクリプト セクションを追加します。
3. このセクションを People と呼びます。
4. 次のスクリプトを入力します。

//Add dummy people data
PeopleTemp:
LOAD * INLINE [
PersonID, Person
1, Jane
2, Joe
3, Shawn
4, Sue
5, Frank
6, Mike
7, Gloria
8, Mary
9, Steven,
10, Bill
];
 
//Add dummy age data
AgeTemp:
LOAD * INLINE [
PersonID, Age
1, 23
2, 45
3, 43
4, 30
5, 40
6, 32
7, 45
8, 54
9,
10, 61
11, 21
12, 39
];
 
//LOAD new table with people
People:
NoConcatenate LOAD
    PersonID,
    Person
Resident PeopleTemp;
 
Drop Table PeopleTemp;
 
//Add age and age bucket fields to the People table
Left Join (People)
LOAD
    PersonID,
    Age,
	If(IsNull(Age) or Age='', Dual('No age', 5),
	 If(Age<25, Dual('Under 25', 1),
	  If(Age>=25 and Age <35, Dual('25-34', 2),
	   If(Age>=35 and Age<50, Dual('35-49' , 3),
	    If(Age>=50, Dual('50 or over', 4)
	     ))))) as AgeBucket
Resident AgeTemp
Where Exists(PersonID);
 
DROP Table AgeTemp;

5. [データのロード] をクリックします。

スクリプトでは、Ageおよび AgeBucket項目がロードされるのは、PersonID がすでにデータ モデルにロードされている場合に限られます。

AgeTemp テーブルには年齢のリスト (PersonID 11 と 12) がありますが、これらの ID はデータ モデル (Peopleテーブル) にはロードされておらず、Where Exists(PersonID) 節によって除外されていることに注意してください。この節はまた、 のように書くこともできます。Where Exists(PersonID, PersonID) をクリックします。

スクリプトの出力は次のようになります。



PersonID が AgeTemp データ モデルにロード済みのテーブルにない場合、Age と AgeBucket項目は People テーブルには結合されません。Exists()関数を使用すると、データ モデル内に孤立したレコード/データが残りません。つまり、Ageと AgeBucket項目に関連する従業員はいないことになります。

6. 新しいシートを作成し、名前を付けます。
7. その新しいシートを開き、[シートの編集] をクリックします。
8. 軸 AgeBucket を含むシートに標準的なテーブルを追加し、ビジュアライゼーションに「Age Groups」という名前を付けます。
9. 軸 AgeBucket とメジャー Count([AgeBucket]) を持つシートに棒グラフを追加します。ビジュアライゼーションに Number of people in each age group という名前を付けます。
10. テーブルと棒グラフのプロパティを希望に応じて調整し、[完了] をクリックします。

    シートは次のようになります。

    

Dual() 関数は、文字列に数値を割り当てる必要があるスクリプトやチャートの数式で役に立ちます。

上記のスクリプトでは、年齢をロードしているアプリケーションがあり、この年齢をバケットに入れ、この年齡バケットと実年齡に基づくビジュアライゼーションを作成します。従業員 25 歳未満、25～35 歳といったバケットがあります。Dual() 関数を使用すると、後でリスト ボックスやチャート内で年齡バケットをソートするために便利な数値を、年齡バケットに割り当てることができます。つまりアプリ シートでは、ソートによってリストの終端に「No age (年齡なし)」が追加されます。