QlikViewのマスタカレンダー作成のベストプラクティス

QlikViewのロードスクリプトはETLツールとしてもとても優れていると思う。
ただ、ひらすらコーディングするしかないからテクニックしだい。
かなり奥深い。

これが定石と思っていた書き方ものが、ある日覆ってしまうことだってある。
今回紹介するマスタカレンダーもそう。

#マスタカレンダー作成のスタンダードなロードスクリプト

Qlik社推奨というか、一般的なマスタカレンダーはこんなふうに書く。

MasterCalendar_old

LET vCustomerCnt =100000;
LET vProductCnt = 10000;
LET vCategoryCnt = 10;
LET vTranCnt = 100000000;
LET vStoreCnt = 10;
LET vDateStart = 43831;
LET vDuration = 365 * 10 + 2;

Transaction:
LOAD RowNo()										as tran_id,
	 Date($(vDateStart) + Floor((Rand() * $(vDuration))),'YYYY/MM/DD') as date, 
	 Ceil(Rand()*$(vCustomerCnt))					as customer_id,
	 Ceil(Rand()*$(vStoreCnt))						as store_id,
	 Ceil(Rand()*$(vProductCnt))					as product_id,
	 Mod(Floor(pow(Rand(),2)*10),3)+1				as unit,
	 Ceil(Sqrt(Rand()*10000000))					as amount
AutoGenerate $(vTranCnt);

tmp_TBL:
LOAD Max(date) as maxdate,
	 Min(date) as mindate
Resident Transaction;

LET varMaxDate = FieldValue('maxdate',1);
LET varMinDate = FieldValue('mindate',1);

MasterCalendar:
LOAD Date($(varMinDate) -1 + IterNo(),'YYYY/MM/DD') as date,
	 Year($(varMinDate) -1 + IterNo())				as year,
	 Month($(varMinDate) -1 + IterNo())				as month,
	 Date(MonthStart($(varMinDate) -1 + IterNo()),'YYYY/MM')				as yearmonth
AutoGenerate 1
While $(varMinDate) -1 + IterNo() <= $(varMaxDate);	 

僕はこんなふうに習ってきたし、人にもこの通りに教えてきた。
プリシーディングロードやAutoGenerate、FieldValueなどを教える時にとても良いコードだと思う。

が、データ件数が多い（例えば1億件くらいの）時、

tmp_TBL:
LOAD Max(date) as maxdate,
Min(date) as mindate
Resident Transaction;

この箇所が、どうしても時間がかかってしまう。
上記のリロード（Transactionが1億件）の場合だと、MasterCalendarの作成に手元のPCでは実に5分28秒もかかってしまう。
内訳は上記のtmp_TBLのLOADのMax(),Min()の処理時間が大半を占めており、MasterCalendar: Load ... は一瞬で終わっている。

#マスタカレンダーのベストプラクティス

先日、素敵なコードに出会った。
QlikView COOKBOOK: BETTER CALENDAR SCRIPTS

なんすかこれ？
本当かな？
こんなコード見たことない。

で、実際やってみたら瞬時にMasterCalendarを作成できた。
そのコードがこれ。

MasterCalendar_new

LET vCustomerCnt =100000;
LET vProductCnt = 10000;
LET vCategoryCnt = 10;
LET vTranCnt = 100000000;
LET vStoreCnt = 10;
LET vDateStart = 43831;
LET vDuration = 365 * 10 + 2;

Transaction:
LOAD RowNo()										as tran_id,
	 Date($(vDateStart) + Floor((Rand() * $(vDuration))),'YYYY/MM/DD') as date, 
	 Ceil(Rand()*$(vCustomerCnt))					as customer_id,
	 Ceil(Rand()*$(vStoreCnt))						as store_id,
	 Ceil(Rand()*$(vProductCnt))					as product_id,
	 Mod(Floor(pow(Rand(),2)*10),3)+1				as unit,
	 Ceil(Sqrt(Rand()*10000000))					as amount
AutoGenerate $(vTranCnt);

MasterCalendar:
LOAD tmp_date 										as date,
	 Year(tmp_date)									as year,
	 Month(tmp_date)								as month,
	 Date(MonthStart(tmp_date),'YYYY/MM')			as yearmonth;
LOAD
	Date(mindate + IterNo()) 						as tmp_date,
	maxdate
WHILE mindate + IterNo() <= maxdate;
LOAD min(FieldValue('date',RecNo()))-1 				as mindate,
	 max(FieldValue('date',RecNo())) 				as maxdate
AutoGenerate FieldValueCount('date');

結果が素敵、1秒以内で終わった。ほんとに一瞬。
これ考えた人スゴい。
これはマスタカレンダー作成の革命的な出来事だ。