Alteryx：表の全データをR言語ツールで文字列結合して、ODBCのクエリの一部を動的に書き換える

何のこっちゃ？てなタイトルですが、Alteryxの純正ツールが「使えない」場合、R言語でゴリゴリコードを書くしかないという、諦めにも似た愚痴のようなものです。

やりたいことは、例えばビッグデータを格納したDatabaseにアクセスして、前日のKPI(Key Performance Indicator)から対象を絞る。その対象だけ先週いっぱいのKPIを取得したい。この時、クエリを動的に書き換える必要がありますが、

(1) Dynamic InputツールにはクエリのWhere句の中身を書き換える機能がある
(2) しかし、Whereが複数条件の場合に選択できない
(3) そこで、文字列の置換機能を利用してWhere ~ in ()内に条件を埋め込む
(4) しかし、動的な文字列の結合がAlteryx純正ツールではうまくできない

このような問題があるのです。もう少し単純な例で示すと、

Select name from 廃涸村 Where 体力 > 10000

の様なクエリから以下のような表を吐き出し、

name
太郎
次郎
三郎

→Select * from 家族構成一覧 where name in ('太郎', '次郎', '三郎')

このようなクエリを作りたい場合、対象が３つと分かっているならばFormulaツール等で簡単にできますが、クエリの結果なので毎回変化します。標準ツールを色々と試してみましたが、結局Rツールでコードを書く方法しか見つかりませんでした。

全体図を下に示し、各ツールの動作について解説します。

Input Data & Select

ここで前段階のクエリ結果（Dynamic Input & Selectを使用）や、Excel/CSVファイルから対象リストを読み込みます。

Unique

リストの重複を省きます。Unique Fieldsでキーとなる項目を選択します。後記シングルクォーテーションの追加も含めて、Rツールでも処理が可能ですが、なるべくコードはシンプルにしたかったので…（一応コードフリーのツールだからね）。

Formula

今回はWhereで絞るのが文字列だったので、シングルクォーテーションで囲みます。

Output Column : my_id
Formula : "'"+[my_id]+"'"

R Tool

R言語ツールはタコ足の形をしています。多入力多出力が可能で、コード内でどの入出力かを選択・処理します。これが最初分からなかった。何か特別な出力項目が５個あるの？と思ってしまって。コードは以下。

ids <- read.Alteryx("#1", mode="data.frame")
write.Alteryx(paste(ids[,1], collapse = ","), 1)

１行目：変数idsに、#1のインプットからデータを取得します。#1の文字は分かりやすい文字に変えることができます。
２行目：idsの1列目[,1]をpaste関数で結合し、コンマ区切りにして、1へ出力します。

全データの結合は、Multi-Row Formulaだと前後数行としか結合できず、Cross Tabで横並びにした後に結合する方法では、固定列数しか処理できません（[column1] + [column2] + [column3]）。

2019/7/9 Update!!
R Toolで出力する時、上記のように項目名を指定しないと、自動で項目名が付けられてしまいますが、この後のDynamic Renameではそれに追従してリネームできないことが分かりました。よって、Rの中で項目名を明記しないと次に繋がりません。

id_read <- read.Alteryx("#1", mode="data.frame")
id_pasted <- paste(id_read[,1], collapse = ",")
names(id_pasted) <- c("enodeb_id_list")
write.Alteryx(id_pasted, 1)

変数名を分かりやすくしています。names(id_pasted) は id_pasted の項目名一覧を表し、ここに c("columnName") を入れることで項目名が変わります。今回は１列のみのテーブルなので一つの列名のみですが、複数の場合は c("columnName1", "columnName2", ...) などと指定します。

Dynamic Rename

Rからの出力は、値により自動で項目名が付けられてしまうため、SelectツールのRenameは使えません。値を長々と結合した項目名になってしまうのです。Rで項目名を指定する方法が分からなかったので（Alteryx Communityでも未解決だった）、Dynamic Renameツールで強制的に変更することにします。

Expression : my_id_list

Dynamic Input

ここで、クエリを指定してデータベースから情報を取得します。今回は、Where ~ in ()のカッコ内を置換することで、動的に対象を変化させます。

ODBC Database
Table or Query : Select * From my_table Where user_id in (ID_LIST)

オプションで文字列の置換を指定、ID_LIST部分にRツールで作成した対象リストを入れます。

Modify SQL Query - Replace a Specific String
Text To Replace : ID_LIST
Replacement Field : my_id_list

Text Input & Formula & Append Fields

こちらはExcel形式のファイル名に日付を追加するTipsです。「パス～ファイル名|||シート名」と指定しなければならないので、Append/Prependで日付を追加する方法が分かりません（不可能？）。そこで、Formulaで直接作ってAppend Fieldsで項目追加した後に、Output ToolでTake File/Table Name From Fieldにチェック、Change Entire File Pathを選択し、Fieldにその項目を指定した方が、フレキシブルに設定できます（例えば、ファイル名に日付を入れて、対象ごとにシートを分ける等）。パスは相対パスを指定していますが、ファイルシステム関数でInputファイルの親フォルダを取得、なんてことも可能です。

Formula :
'.\' + DateTimeFormat(DateTimeNow(),'%Y%m%d_%H%M')
 + '_kpi_output.xlsx|||KPI'
（時分まで含めれば、１分以内で繰り返さない限り、ファイルの重複は発生しない）

まとめ

クエリの動的更新の他にも、ファイル名をFormulaで更新する方法を紹介しました。見ての通り、コードフリーのGUIツールと言いながら、クエリや関数の知識が無いと躓くこと多々。Ｒ言語なんて初めて触れましたし。ただ、ビッグデータをOfficeソフトで扱うのは現実的ではないので、Alteryxの出番となるでしょう。