xobQuery(ゾブクエリ)を活用しよう!

xobQuery(ゾブクエリ)は、xoBlosに標準機能として組み込まれている便利な仕組みであり、以下のようなことができます。

  • Excelブック、XMLCSVの入出力・変換を、制御シートの仕組みを使わないで実現。
  • 複数CSVファイルのXMLファイルへの変換など、設定が難しくなりがちな処理を1手順で実現。
  • クエリ(SQL)を記述することによる、データのDB処理を実現。

今回の記事では、xobQueryの紹介がてら、ビジネスシーンではよくある、データのマッチングへの応用をとりあげます。

重点顧客のみの販売データを出力する処理の青写真

f:id:xoblos:20170625235538p:plain

Excelシートで販売データ(八丁堀水産_販売データ.xlsx)を入力

f:id:xoblos:20170625235556p:plain

Excelシートで重点顧客リスト(重点顧客リスト.xlsx)を入力

f:id:xoblos:20170626001151p:plain

DesignerでxobQuery手順を設定(2つのExcelブックの入力手順)

f:id:xoblos:20170626002019p:plain

実行する手順     ::\helper\xobquery.exe
コマンドライン引数  -i INPUT\八丁堀水産_販売データ.xlsx::データ/data -h -d DB\sales.db

-i INPUT\八丁堀水産_販売データ.xlsx::データ/data
   INPUTフォルダの八丁堀水産_販売データ.xlsxの「データ」シートを入力し、DBの「data」テーブルに投入
-h
   Excelシートの1行目(ヘッダー項目)を、テーブルのカラム名にする
-d DB\sales.db
   DBフォルダのsales.dbに格納

f:id:xoblos:20170626002036p:plain

実行する手順     ::\helper\xobquery.exe
コマンドライン引数  -i INPUT\重点顧客リスト.xlsx::リスト/major -h -d DB\sales.db

-i INPUT\重点顧客リスト.xlsx::リスト/major
   INPUTフォルダの重点顧客リスト.xlsxの「リスト」シートを入力し、DBの「major」テーブルに投入
-h
   Excelシートの1行目(ヘッダー項目)を、テーブルのカラム名にする
-d DB\sales.db
   DBフォルダのsales.dbに格納

入力実行後にDB(sales.db)への格納状態を確認

xobQueryで使用しているDB形式はSQLiteなので、SQLite形式DBの内容を表示するフリーウェアで確認できます。下図の例では、SQLiteSpyを使って表示しています。

「data」テーブル

f:id:xoblos:20170626004813p:plain

「major」テーブル

f:id:xoblos:20170626004823p:plain

クエリ(SQL)手順を設定

f:id:xoblos:20170626010642p:plain

実行する手順     ::\helper\xobquery.exe
コマンドライン引数  -d DB\sales.db -Q SQL\select_major.sql -o OUTPUT\重点顧客販売データ.xlsx::データ -H

-d DB\sales.db
   DBフォルダのsales.dbを使用
-Q SQL\select_major.sql
   SQLフォルダのselect_major.sqlファイルを、クエリとして実行
-o OUTPUT\重点顧客販売データ.xlsx::データ
   OUTPUTフォルダの重点顧客販売データ.xlsxの「データ」シートへ出力する
-H
   Excelシートの1行目(ヘッダー項目)へ、テーブルのカラム名を出力する

SQL\select_major.sql の内容

/***
 dataテーブルから、major.顧客名 にあるデータを抽出
***/

SELECT * FROM data INNER JOIN major 
	ON data.顧客名 = major.顧客名;

出力Excelブック(重点顧客販売データ.xlsx)

オートフィルタをかけてみると、重点顧客リストにある顧客のみが抽出されていることがわかります。

f:id:xoblos:20170626014737p:plain

「スタートアップ」フォルダはどこにある? まごついたときのショートカット

Windowsの「スタートアップ」フォルダにプログラムを登録したいとき。 例えば、xoBlosServerHost のショートカットを登録したいときなどです。

OS(Windows)のバージョンにより、「スタートアップ」フォルダを表示させるUIがまちまちなため、まごつくことがあります。

そんなときは、次のショートカットをお試しください。

Windowsキー+R
  ↓
「shell:startup」を入力
  ↓
[Enter]キー

f:id:xoblos:20170622145818p:plain

変換入力セクション

二つの役割。

生成のセクション「変換入力」は、大きく二つの記載方法で分かれます。キーワード『ファイル』に設定したファイルとマッチングをかけ、情報を得るのが一つ。そしてもう一つはキーワード『直データ』を使った、制御シート上にルールを明記する方法です。

混ぜて活用する

f:id:xoblos:20170613183251p:plain

上図は、各方法の組み合わせの例になります。緑枠内の塗りつぶし色は、見易いように付けており、それぞれが関係していることを示しています。

「#」始まりは変数(お皿)ですが‥‥。①まず基本入力の「支店マッチ」は、同セクションのキーワード『固定値』により、「0」が初期値になっています。②次に上側の変換入力の「Matched」は、「master.xml」に存在しない項目であるため、変数としています。③続いて基本入力の「支店コード」と上側の変換入力の「部署コード」がマッチしたら、「部署名」は取得できますが‥‥それは同時に、「Matched」に「1」があて込まれることも意味しています。④そして受け皿である「支店マッチ」には勿論「1」が入ることになります。⑤更に下側の変換入力の設定により、基本入力の「支店マッチ」の値を都度見て、「1」であれば「OK」に書き換え、「2」であれば「NG」に書き換えるようにしています。⑥最後にキーワード『高度選択条件』で「'='OK'」としており‥‥。

つまるところこの制御シートは、「master.xml」とマッチしたデータのみ生成するという要件を、こなしていることが明らかになります。

ヘッダー(項目名行)なしのファイルリスト

次のファイルリスト作成の記事に関する補足です。

フォルダの中のファイルリストを作成する(Designer)http://xoblos.hatenablog.jp/entry/2017/04/10/002209

この例では、1行目に「ファイル名」という名前の項目名、2行目以降にファイル名のリスト形式で説明しました。

しかし、リスト形式データの場合、1行目からデータが始まり、項目名の行(ヘッダー行)がない場合もあります。

その場合は、「手順の繰り返し実行の設定」で [1行目はヘッダ行ですか?] を「いいえ」に設定し、$(item:"item1") のように指定して項目を参照できます。("item1" はヘッダー項目の1番目を表します)

上記の記事の例では、batファイルの内容を

dir INPUT /B A-D > filelist.csv

にすれば大丈夫です(ヘッダー行なしでリスト出力されます)。

ファイルの参照順序

複数生成時は、都度前提(雛形)を意識する。

f:id:xoblos:20170526160036p:plain

昨日記事で、上図のような生成結果を列挙しました。「ようし自分も試してみよう!」と勇ましく生成を試みた方の中で‥‥異なる結果が出た方はいらっしゃいますでしょうか。

f:id:xoblos:20170526160044p:plain

生成部分で、制御シートや業務(.xob)ファイルに示さなかったのですが、上図 3つの入力ファイルを取り扱うという設定内容に差が考えられます。

f:id:xoblos:20170526161918p:plain

一つの雛形に対し複数の入力ファイルを取り扱う、或いは複数回生成を行う場合は、各回の生成結果が常に次回の雛形になることを忘れてはなりません。上図「差異.xml」を先頭に指定してしまえば、「菅野」と「大野」が先ず生成され、後の 2ファイルはそれを前提に上書き生成するので、「比較ID」の情報が消えてしまいます。

※この設定を制御シートで行う場合は、キーワード『ファイル』を複数行で書きます。

曖昧(ワイルドカード)指定の注意点

「3つのファイル全部取り扱うから、自分はワイルドカード指定して正常に生成された!」と安心しているそこのあなた。ワイルドカードは便利ですが、順序を問うような要件が係る場合は、軽んじてはなりません。

f:id:xoblos:20170526160051p:plain

上図のような設定は、確かに 3つの入力ファイルを取り扱う設定になっています。しかし、参照順序は実行しているシステムに依存します。この為、上図設定で「うまくいった!」という方と「うまくいかなかった!」という方と、分かれることは充分あり得ます。

確かな設定は、一つ一つ正しい順序でファイル設定するか、通し番号を各データに割り振ってソートするかの、いずれかになります。生成の手順を入力ファイル数分、用意するのは現実的ではありません。

データの比較

XmlMatcher.exe のお仕事。

Designer も corabo も、インストールフォルダ内に[helper]という階層があり、そこに「XmlMatcher.exe」は居ます。当プログラムは業務(.xob)ファイル内でいう、『抽出データ比較』手順にて活躍します。

f:id:xoblos:20170525150012p:plain

上図左を変更前、右を変更後としてそれぞれ単純抽出します。各 xml ファイルを、下図のように『抽出データ比較』手順で設定します。

f:id:xoblos:20170525150021p:plain

この手順を実行すると、比較結果の xml ファイルが作成されます。

f:id:xoblos:20170525151840p:plain

各抽出データは、生成要件に合わせて要/不要が分かれます。下記を前提に、いくつか例を挙げてみましょう。

:「変更前.xml:「変更後.xml:「差異.xml

差異情報のみ欲しい

『抽出データ比較』手順は、差異あるデータのみ抽出します。そのため、ここで必要な抽出データは③のみになります。

f:id:xoblos:20170525150033p:plain

差異の有無問わず全体の情報が必要

よくあるケースとしては、差異分析はするけれど出力内容はあくまで全体というものになります。しかし前節のとおり、③は差異の情報しか持っていません。全体情報(全員の名前)を得るためには、①と②のデータも基本入力データとする必要があります。

f:id:xoblos:20170525150041p:plain

生成先の差異情報は行(データ)単位で示したい

更によくあるケースとしては、全体の情報にプラスして結局人一人の新旧情報がどうであったのかも示すというものです。無論、このケースでも①~③いずれのデータも使用することになります。下図の作成例は、どちらかと言えば中間処理寄りですね。

f:id:xoblos:20170525161946p:plain

それぞれの生成結果は下図のとおりになります。各抽出データの在り方を意識し、様々な帳票要件をこなしてください!

f:id:xoblos:20170525150103p:plain

空白の情報を許さない

空白情報を操作するタイミング。

ある日、Aさんと Bさんは地元一番のお食事処を知るために、手分けして調査をしました。調査後、その結果を一覧化することになったようです。

f:id:xoblos:20170511140716p:plain

必要としているのは確かな情報なので、不毛な収穫の反映は避けたいところです。上図、生成の雛形シートに関する制御シートは、以下例のように作成します。

f:id:xoblos:20170511140726p:plain

ここでのポイントは二つ。一つは、「情報1」「情報2」という異なる項目であるにも拘わらず、『セット先セル』の内容が同じということ。もう一つは、キーワード『空データでの上書禁止』の「禁」設定です。

これらは、「情報1」と「情報2」のいずれもセル「B2」へ順に出力させるけれども、どのタイミングで空白が来ても、値を持った内容を優先的に出力させるという仕組みになっています。

f:id:xoblos:20170511140732p:plain

生成してみました。E店の情報は二人とも得られなかったようなので、空白がくるのは仕方がありません。

D店は、後勝ちで Bさんの情報が反映されています。‥‥確かな情報としては、「駅から遠い」も必要かもしれません。他キーワードや関数で、Aさんと Bさんの情報をまとめた上で出力する仕組みを考えてみてください。

抽出元データの空白は、未調査であったのか余りに不評でノーコメントであったのかが掴めません。何かに徹底しているのであれば、生成の雛形シートのセル「B2」に、事前に固定情報をいれておけば、『空データでの上書禁止』の活躍は広がります。

抽出時点で空白の内容を固定化:キーワード『データが無いときの値』を使用。
生成時点で空白の内容を固定化:雛形シートに予め固定情報をいれておく。
                ※前回記事も参照。