こんにちは。
先週はワクチン接種の副反応でダウンしてました。ヤマダです。
頭痛がしんどかったです。。
さて、今回は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のスクリプトが作れれば他は問題ないかと思います。
稀に見かけるんですよね、こういうデータ。
その際には参考にしていただければ幸いです。
今日はこの辺で。ではでは。