Buatkan tabel berdasarkan tagihan kartu kredit yang dilampirkan, dengan detil sebagai berikut.

Struktur tabel

Tabel memiliki field-field berikut:

• nomor: Nomor kartu kredit
• jaringan: Jaringan kartu kredit (Visa, Mastercard, GPN, JCB, BCA, dll.), gunakan 6 digit pertama dari nomor kartu kredit untuk menentukan jaringan kartu kredit.
• penerbit: Penerbit kartu kredit (BCA, Mandiri, BNI, BRI, dll.), gunakan 6 digit pertama dari nomor kartu kredit untuk menentukan penerbit kartu kredit.
• nama: Nama pemilik kartu kredit
• tanggal_auth: Tanggal auth atau tanggal transaksi (field berjenis date)
• tanggal_capture: Tanggal capture atau tanggal settlement (field berjenis date)
• keterangan: Keterangan trasaksi sesuai dengan yang tertera pada tagihan, tanpa modifikasi
• keterangan_cleansing: Keterangan setelah data cleansing (berdasarkan aturan data cleansing di bab khusus untuk ini)
• kategori: Kategori transaksi (lihat bab khusus untuk ini)
• kategori_lain: Kategori transaksi apabila diluar dari kategori yang sudah ditentukan (lihat bab khusus untuk ini)
• mall: Nama mall tempat transaksi dilakukan, jika tidak ada informasi ini, kosongkan field ini.
• kota: Kota lokasi transaksi
• negara: Negara lokasi transaksi, dalam bentuk kode negara dua karakter.
• nilai_rupiah: Nilai transaksi dalam rupiah (field dengan jenis data currency)
• nilai_valas: Nilai transaksi dalam mata uang asing (hanya jika transaksi dilakukan menggunakan mata uang asing, kosongkan jika transaksi bukan dalam mata uang asing) (field dengan jenis data currency)
• nilai_tukar: Nilai tukar mata uang (hanya jika transaksi dalam mata uang asing, kosongkan jika transaksi bukan dalam mata uang asing) (field dengan jenis data currency)

Data Cleansing

Berikut adalah aturan-aturan untuk data cleansing. Lihat isi dari field keterangan, lalu gunakan aturan berikut ini untuk melakukan normalisasi agar data mudah diklasifikasikan.

• Jika mengandung "gopay" atau "gojek", normalisasi menjadi "GOTO"
• Jika mengandung "grab*", normalisasi menjadi "GRAB"
• Jika mengandung "shopee", normalisasi menjadi "SHOPEE"
• Jika mengandung "blibli.com", normalisasi menjadi "BLIBLI"
• Jika mengandung "cicilan bca", hapus informasi tersebut, lalu ganti dengan informasi merchant transaksi, jika informasi tersebut ada pada keterangan. jika tidak ada informasi merchant, gunakan saja "CICILAN BCA"
• Jika transaksi berasal dari PAYPAL, misalnya "PAYPAL *FOO", normalisasi menjadi "FOO"
• Jika transaksi mengandung nomor identifier, kode cabang, kode challange, dsb yang terlihat random seperti "7UXHEGPGWTUAAV", hapus nomor tersebut.

Keterangan setelah cleansing digunakan pada field "Keterangan setelah data cleansing", biarkan field "Keterangan" apa adanya sesuai dengan yang ada di tagihan.

Kategorisasi Transaksi

Gunakan kategori-kategori berikut untuk mengkategorikan transaksi:

• Streaming: untuk transaksi yang berhubungan dengan layanan streaming seperti Netflix, Vidio, Spotify, dll.
• Marketplace: untuk transaksi yang dilakukan di marketplace seperti Tokopedia, Shopee, Blibli, dll.
• Transportasi: untuk transaksi yang berhubungan dengan transportasi seperti KCIC atau Grab.
• Restoran: untuk transaksi yang dilakukan di restoran atau tempat makan.
• Internet: untuk transaksi yang berhubungan dengan layanan internet seperti Biznet.
• Cloud-Hosting: untuk transaksi yang berhubungan dengan layanan hosting dan nama domain seperti Digital Ocean, AWS, Google Cloud, dll.
• App: untuk transaksi yang berhubungan dengan aplikasi online seperti Google Play, App Store, dll.
• Lain-lain: untuk transaksi yang tidak masuk dalam kategori di atas. khusus untuk ini, gunakan penilaian subjektifmu untuk menentukan kategori yang tepat, lalu simpan hasilnya pada field kategori_lain.

Perlakuan khusus:

• Untuk transaksi 'GOPAY-GOJEK', gunakan kategori 'Transportasi'

Untuk saat ini gunakan pertimbanganmu sendiri untuk membuat kategori untuk setiap transaksi.

Lain-lain

Jika kota adalah "JAKARTA SLT", ganti menjadi "JAKARTA SELATAN".

Contoh kasus grab dengan keterangan transaksi "GRAB* A-7TUG86VWW9M7AV JAKARTA SLT", maka keterangan adalah "GRAB* A-7TUG86VWW9M7AV JAKARTA SLT", keterangan setelah cleansing adalah "GRAB", dan kota adalah "JAKARTA SELATAN". Nilai "A-7TUG86VWW9M7AV" dihapus, dan hanya ada pada field keterangan saja.

Output

Untuk output, saya minta dua jenis output.

Output 1: File Excel

Buatlah file Microsoft Excel berisi data-data sesuai penjelasan di atas. Namakan sheet menjadi "Data".

Output 2: SQL untuk duckdb

Buatlah data dalam format SQL yang kompatibel dengan duckdb, agar bisa diimport ke database tersebut untuk diolah lebih lanjut.