チャレンジ #150: International Bank of Awesome Data - パート I

[NicoleJohnson]

先週のチャレンジの解答は こちらで見つけることができます。

今週からウィークリーチャレンジはACEプログラムとの新しいパートナーシップを開始します。 今週から4週間にわたり @NicoleJohnson がウィークリーチャレンジを出題します。楽しんでいただければ幸いです。

International Bank of Awesome Dataは、最も重要であるデータに焦点を当てる能力に誇りを持っている新しい銀行です。世界中に銀行の支店（Bank Branch）があるIBADは、実際にデータを読み取ることができれば、真に世界的なフォースとなることでしょう。

ところが、実際にはデータは乱雑で、本当に素晴らしいデータではあるのですが、それを使って何かをする前に、クリーンアップする必要があります。それでは、基本的なデータ準備の演習から始めましょう。IBAD本社に提出された銀行取引ファイルのサンプルを使用して、世界中の12の銀行支店（Bank Branch）の月次レポートを作成しましょう。

チャレンジ:
各支店は、データ抽出に独自のフォーマットを使用しているようです。したがって、各ファイルには月次レポートに必要な情報が含まれていますが、リージョン(Region)ごとにすべて異なるフォーマットでファイルが作成されているため、解析してクリーンアップして一貫性のあるフォーマットにする必要があります。

データ出力を標準化するために適用する必要のあるいくつかのルール:
-　顧客IDを除くすべてのフィールドから先頭の0および/または末尾の空白を削除してください（顧客IDは常に12文字で、スペースは使用せず、必要に応じて先行0を使用する必要があります）
-　銀行口座番号は常に6桁– 8桁– 2桁のパターンに従う必要があります（123456-12345678-12）
-　トランザクションDateTimeは標準のAlteryx日時形式（yyyy-mm-dd HH：MM：SS）である必要があり、フィールドのデータ型はDateTimeである必要があります。
-　Debit / Creditフィールドがない場合、Amountフィールドに-記号がある場合はCredit（C）であると見なすことができます。それ以外の場合は、デフォルトでDebit（D）になります。 **
-　Transaction ClearedIndicatorのデータ型はブール値である必要があります。

**現時点では、[Amount] フィールドと[Opening Balance] フィールドを変更する必要はありません。これらのフィールドは来週のチャレンジでクリーンアップします。**

[gawa]

リージョンBの25行目の日付がだけ合いませんでした。

スポイラ

👇25行目のデータから日付をパースすると、2021.9.6か2021.9.26の2パターンあり得ます。他の行もこのパターンはあるのすが、その場合、日付が大きくなる方向にパースしましたが、この25行目はそれだとダメなようです。色々考えたのですが、日付を取り出すパターンが見つからず、他の方の回答を参考にしたいです・・・。

[sanae]

今回はエクストラクレジットまでできました！いろんな解き方ができそうですね。

スポイラ

■メインフロー

■マクロ（リージョンC用）

[AkimasaKajitani]

できました！なかなか歯ごたえありですね・・・。

スポイラ

こちらはエクストラクレジットをわざと回答どおりにしたバージョンです（Challenge_150_ja_Start File_AK2.yxzp）。

本来あるべき姿でエクストラクレジットを作ると（Challenge_150_ja_Start File_AK.yxzp）、

となると思います。

回答例の方を見ると、Opening Balanceの18レコード目のパースでミスってますね・・・。

本来は、こうなるべきな気がします（18行目だけ行の長さが1文字分長い）。

ACEシリーズ、おもしろいです！

[Qiu]

Parse or not parse, that is the question. 😁

@SaoriG お休みですかね。

@NicoleJohnson 日本語もできるですね。👍

スポイラ

[DaisukeTsuchiya]

今回は久しぶりにしびれました。かなり面倒でしたが、正規表現と日時変換の勉強になりました。

スポイラ

@AkimasaKajitani  のご指摘どおり、エクストラクレジットでは18行目がずれたままですが諦めました。
AM、PMの換算とか、色々なところに落とし穴がある問題でした。

 

[ks4050kt]

列分解ツールを使うのはカッコ悪いと思っている質なので、正規表現でやりましたが。Region B の Date の抜き出しだけはいい感じのルールを見つけることが出来ず、最終的に出力例に合うように Formula Tool で化粧直しをしました。(出力例の結果からの逆引きではなく 日付部分のルールを見つけた方こっそりとスポイラ返信で教えて下さい)

スポイラ

[ntakeda]

フィールド名の規則性がわからなくてズルしました。

それでも、Date Timeが１か所合わなかったです。

スポイラ

[Mukai]

投稿します。

スポイラ

AとBにものすごく時間を取られてしまったので、Ｃは諦めました。

[Yoshiro_Fujimori]

回答します。

スポイラ

Region A

Region B
1件 一致しませんでした。
IBANのフィールド長は国により異なるので、IBANの国とフィールド長の情報がなければどちらが正しいかは判別できません。
  Field:Transaction DateTime Row:25 
  Expected:2021-09-06 16:10:36 
  Actual:2021-09-26 16:10:36

Region C
1件一致しませんでした。
  Field:Opening Balance Row:18
  Expected:0 82002,9
  Actual:82002,9
先頭の0を除くのであれば、Actualで合っていると思います。
（この行は、ひとつ前のフィールドから既に１桁ずれていますね。後でクレンジングするのでしょうけど。）