h.yamada/ 2022年 5月 16日/ 技術

こんにちは。
先週はワクチン接種の副反応でダウンしてました。ヤマダです。
頭痛がしんどかったです。。

さて、今回はQlikのロードスクリプトの処理についてです。

データの横持ちを縦持ちに変更する場合はCrossTableで変換ができますが、
1つの項目にn個の値がセットされている場合はちょっと面倒です。
今回はこちらの処理方法について記載しようと思います。

今回やること

今回はcsvのデータを用意しました。(1行目はヘッダ)

"大分類","中分類"
"肉","豚,鶏,牛,加工肉"
"飲料","水,ジュース,酒,炭酸"
"魚","生魚,塩干"
"麺","うどん,そば,ラーメン,そうめん"

ゴール:中分類の項目を分割し、レコードも別にする(1行目はヘッダ)

"大分類","中分類"
"肉","豚"
"肉","鶏"
"肉","牛"
"肉","加工肉"
"飲料","水"
"飲料","ジュース"
"飲料","酒"
"飲料","炭酸"
"魚","生魚"
"魚","塩干"
"麺","うどん"
"麺","そば"
"麺","ラーメン"
"麺","そうめん"

処理

中分類の区切り文字の最大値を取得

データをロード後、SubStringCountで中分類の区切り文字(今回はカンマ)の出現回数の最大値を取得します。
(今回の最大値は「3」となります)

t1:
LOAD 大分類, 
     中分類
FROM
[data.csv]
(txt, codepage is 932, embedded labels, delimiter is ',', msq);

t2:
LOAD
    max(SubStringCount(中分類, ',')) as max_count
Resident t1;

中分類を区切るスクリプトを作成

for文でループして、スクリプト文を作成します。

LET script = '';
for i = 1 to FieldValue('max_count', 1) + 1
    if i = 1 THEN
        LET script = script & 'SubField(中分類,'','',' & i & ')  as 中分類' & i;
    ELSE
        LET script = script & ',' & chr(13) & 'SubField(中分類,'','',' & i & ')  as 中分類' & i;
    ENDIF
NEXT

作成されたscript変数の中身はこのようになります。
SubField関数で抽出する場所(1~4)と項目名(中分類1~4)が順に定義されます。
これで項目を抽出して横持ちするスクリプトができました。

SubField(中分類,',',1)  as 中分類1,
SubField(中分類,',',2)  as 中分類2,
SubField(中分類,',',3)  as 中分類3,
SubField(中分類,',',4)  as 中分類4

魚の行の中分類3,4は抽出する値がないのでNullになります。

CrossTableを使用してデータを横持ちから縦持ちにする

script変数を使用してロードします。
その際にCrosstableを使用してデータを横持ちから縦持ちに変換後、不要なテーブル、項目を削除します。

t3:
CrossTable(col, 中分類, 1)
LOAD
    大分類,
    $(script)
Resident t1;;
DROP Table t1 ,t2;
DROP Field col From t3;

まとめ

用途に応じてSubFieldのスクリプトが作れれば他は問題ないかと思います。
稀に見かけるんですよね、こういうデータ。
その際には参考にしていただければ幸いです。

今日はこの辺で。ではでは。

About h.yamada

BIやってます。 初めに触ったBIはPentaho、一番長いのはQlikView、Tableauも少々。 Tableau Desktop Certified Associateの資格も持ってました(有効期限切れ)