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

9912 ワード
Qlik_Sense QlikView QlikSense Qlik 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秒以内で終わった。ほんとに一瞬。
これ考えた人スゴい。
これはマスタカレンダー作成の革命的な出来事だ。