投稿日:

Qlik便利関数 : データの一時保存

「巨大なデータを扱っていて、途中でコケた場合、また最初から回さなければならない。途中段階で一時的にデータを保存したいなぁ」というときに役立つ便利関数です。

benri.txtダウンロード

使い方

/*---------------*/
重たい処理
いろいろなデータ処理
いろいろなデータ処理

call Benri.MyStore('テーブル名');
/*---------------*/
call Benri.MyLoad('テーブル名');

といった感じで使います。

まずそのまま回すと、[c:\temp]にQVDとしてデータが保存されます。2回目以降は、1つ目の「/*————-*/」の右端を消してあげれば、MyLoadの直前までコメントアウトされ、重たい処理をすっ飛ばして一時保管されたデータが読み込まれます。

サンプルプログラム

最初はそのまま回します。すると、testdataがc:\temp\QVW名_testdata.qvdとして保存されます。

2回目以降は、「★」部分の右端「/」を削除して回します。すると、MyLoadまでの処理がすっ飛ばされて、MyLoadによってtestdataが読み込まれます。

ただし、すっ飛ばしたい部分までは別のコメントアウト「/*—-*/」は使えなくなります。すっ飛ばしたい部分内にコメントを残す場合は、「//」を使ってください。

//便利関数の読み込み
$(Include=C:\temp\Benri.txt);

/*------------*/ //←★次に回すときは、右端の「/」を消して回す
//テストデータ
testdata:
	load		*
	Inline [
		prod, ym  , a1, a2
		魚, 201901, 10, 10
		魚, 201902, 20, 
		魚, 201903,   , 30
	]
;

//使用例
call Benri.MyStore('testdata');
/*-----------*/
call Benri.MyLoad ('testdata');

仕様

以下のような内容です。超絶シンプル♪


sub Benri.MyStore(__tbl)
	let __aaa =  Left(DocumentName(), len(DocumentName())-4) ;
	store $(__tbl) into [c:\temp\$(__aaa)_$(__tbl).qvd](qvd) ;
	drop Table $(__tbl) ;
	set __aaa=;
end sub

sub Benri.MyLoad (__tbl)
	let __aaa =  Left(DocumentName(), len(DocumentName())-4) ;
	$(__tbl): NoConcatenate
		load * from [c:\temp\$(__aaa)_$(__tbl).qvd](qvd)
	;
	set __aaa=;
end sub
投稿日:

Qlik便利関数：欠損値をゼロ埋め

欠損値に０を入れてくれる便利関数です。複数の変数がある場合は、スペースで区切ります。区切るスペースは1個でも複数個でも大丈夫です。

call Benri.NulltoZero('テーブル名', '変数1 変数2  変数3');

といった感じで使います。変数1/変数2/変数3それぞれの欠損値を０に変換します。

使い方

以下の「benri.txt」をダウンロードして、適当なところに保存してください。

benri.txtダウンロード

サンプルプログラム

ダウンロードしたbenri.txtを c:\temp に保存して実行してみてください。

//便利関数の読み込み
$(Include=C:\temp\Benri.txt);

//テストデータ
testdata:
	load		prod, ym
			  , if(a1='', Null(), a1) as amt1
			  , if(a2='', Null(), a2) as amt2
	Inline [
		prod, ym  , a1, a2
		魚, 201901, 10, 10
		魚, 201902, 20, 
		魚, 201903,   , 30
		魚, 201904, 40, 
		魚, 201905, 50, 50
		魚, 201906, 60, 60
	]
;

//使用例
call Benri.NulltoZero('testdata', '   amt1   amt2   ');

仕様

以下のようなサブルーチン内容です。
構造はいたってシンプルですが、引数（__vars）にスペースが複数個あった場合の処理で結構手間取ってます。


sub Benri.NulltoZero(__tbl, __vars)

	//左右の無駄なスペース除去
	let __vars = Trim('$(__vars)');
	
	//スペースを1つにする
	let __x = Index('$(__vars)', '  ') ;
	do while __x > 0
		let __x = Index('$(__vars)', '  ') ;
		let __vars = Replace('$(__vars)', '  ', ' ');
	loop
	set __x=;
	
	//変数の個数をスペースの個数を基に数える
	let __nVar = len('$(__vars)') - len(Replace('$(__vars)', ' ', '')) + 1 ;

	//処理用の各種文章
	let __s1 = '';
	let __s2 = '';
	let __s3 = '';
	let __s4 = '';
	
	for __i=1 to $(__nVar)
		let __v = SubField('$(__vars)', ' ', $(__i));
		let __s1 = '$(__s1), if(IsNull($(__v)), 0, $(__v)) as __$(__v)'	; //欠損値をゼロに埋めるための文章
		let __s2 = '$(__s2), $(__v)' 									; //ドロップ用
		let __s3 = '$(__s3), __$(__v) as $(__v)'						; //一時名から元の名前にリネーム用の文章
		let __s4 = '$(__s4), __$(__v)' 									; //ドロップ用
	next __i
	set __v=;
	
	//最初の不要なカンマを取る
	for __i=1 to 4
		let __s$(__i) = mid('$(__s$(__i))', 2) ;
	next __i
	set __i=; set __nVar=;
		
	//欠損ゼロ埋め
	__temp: NoConcatenate
		load		*
				  , $(__s1)
		Resident	$(__tbl)
	;
	drop table $(__tbl);
	//元の変数削除
	drop Fields $(__s2) from __temp ;
	//リネームして変数名を戻す
	$(__tbl): NoConcatenate
		load		*
				  , $(__s3)
		Resident	__temp
	;
	drop table __temp ;
	drop Fields $(__s4) from $(__tbl) ;
	
	set __s1=; set __s2=; set __s3=; set __s4=;
end sub
投稿日:

Qlik : 期間累計・直近●ヶ月

最近コロナで「直近1週間の平均感染者数」とかいう単語をよく見るようになりました。

この「直近●●の合計」など、期間累計のやり方についてです。

チャートを使えば、RangeSum/Aboveで期間累計できますが、ロードスクリプトだとそのやり方を知りません。

なので、データハンドリングで対処してます。

私は２つのやり方でやっているのですが、１つはすべての組み合わせを作ってやる方法（デカルト積）、もう一つは「過去からの総合計から●ヶ月前までの総合計を引く」方法です。

先に、テストデータを載せておきます。

//テストデータ
testdata:
	load		*
	Inline [
		prod, ym, amt
		魚, 201901, 10
		魚, 201902, 20
		魚, 201903, 30
		魚, 201904, 40
		魚, 201905, 50
		魚, 201906, 60
		魚, 201907, 70
		魚, 201908, 80
		魚, 201909, 90
		魚, 201910, 100
		魚, 201911, 110
		魚, 201912, 120
		魚, 202001, 10
		魚, 202002, 20
		魚, 202003, 30
		魚, 202004, 40
		魚, 202005, 50
		魚, 202006, 60
		魚, 202007, 70
		魚, 202008, 80
		魚, 202009, 90
		魚, 202010, 100
		魚, 202011, 110
		魚, 202012, 120	
		肉, 201901, 10
		肉, 201902, 20
		肉, 201903, 30
		肉, 201904, 40
		肉, 201905, 50
		肉, 201906, 60
		肉, 201907, 70
		肉, 201908, 80
		肉, 201909, 90
		肉, 201910, 100
		肉, 201911, 110
		肉, 201912, 120
		肉, 202001, 10
		肉, 202002, 20
		肉, 202003, 30
		肉, 202004, 40
		肉, 202005, 50
		肉, 202006, 60
		肉, 202007, 70
		肉, 202008, 80
		肉, 202009, 90
		肉, 202010, 100
		肉, 202011, 110
		肉, 202012, 120	
	]
;

すべての組み合わせを作ってやる（デカルト積）

元のデータに元のデータをどばっと総当たりでくっつけて、期間に絞って集計するやり方です。と、文章では説明が難しいので、以下のサンプルプログラムをご覧ください。過去1年間の期間累計のサンプルです。

2019/01のレコードに2019/01～2020/12のレコードをくっつける、
2019/02のレコードに2019/01～2020/12のレコードをくっつける、
・・・
2020/12のレコードに2019/01～2020/12のレコードをくっつける、
と一時的にデータが膨らみます。
その後、2020/12のレコードは「(ym2が)2020/1~2020/12までのamtをsumして、元のデータにくっつける」という処理の流れになります。

なお、このやり方だと、一時的にデータが膨大に膨れます。なので、巨大データを扱う際は不向きです。

//--- 期間累計のやり方１：デカルト積（総当たり）
temp: NoConcatenate
	load		prod, ym
	Resident	testdata
;
Outer Join
	load		prod, ym as ym2
			  , amt
	Resident	testdata
;

Left Join(testdata)
	load		prod
			  , ym 
			  , sum(amt)		as amt_直近1年
	Resident	temp
	Where		ym2 <= ym
			and ym2 >  ym-100
	Group By	prod
			  , ym
;

drop tables temp ;

過去からの総合計から●ヶ月前までの総合計を引く

こちらのほうが直感的かもしれません。

以下のサンプルプログラムでは、まず期間に関係ない（総）累計を計算し、その後1年前の累計をくっつけ、それを引いています。

データの始めから1年間経っていない部分（201901~201912）は、欠損になっています。

//--- 期間累計のやり方２：直近までの累計から1年前の累計を引く
temp:
	load		prod, ym
			  , if(Previous(prod) <> prod 
			  	  , amt
			  	  , peek(累計) + amt
			  	)				as 累計
	Resident	testdata
	Order By	prod, ym
;

//1年前の累計
left join
	load		prod 
			  , ym + 100 		as ym
			  , 累計				as 累計_1年前
			  
	Resident	temp
;

//引く
left join
	load		prod, ym
			  , 累計 - 累計_1年前	as amt_直近1年_2
	Resident	temp
;

//大元データにつけ戻し
left join(testdata)
	load		prod, ym
			  , amt_直近1年_2
	Resident	temp
;
drop tables temp ;

以上、ロードスクリプトでの累計のやり方でした。