Home Applications dataset-finance

dataset-finance

This application is not supported by InterSystems Corporation. Please be notified that you use it at your own risk.
5
1 reviews
0
Awards
213
Views
20
IPM installs
0
0
Details
Releases
Reviews
Issues
Pull requests
Articles
Dataset of Finance Transactions in a CSV file for SQL LOAD DATA

What's new in this version

YouTube Video now available

dataset-finance

I created this dataset for InterSystems Datasets Contest to present this repository containing code (a Class) and data (a CSV file) of finance transactions to be loaded into a Global with new SQL LOAD DATA command.

Dataset Source and license

The dataset was created by me by running a macro in Excel to export transaction data into a comma separated values (CSV) text file.
I had to write and run a classmethod in dc.data.util class to convert data values from ‘MM/DD/YYYY’ format to ‘YYYY-MM-DD’ format.
This dataset can be used under MIT License.

analyze-dataset-finance

You can analyze the dataset with my other app found here - analyze-dataset-finance

Online Demo

You can find online demo here - demo

YouTube Video

My very first TouTube video can be seen here - video

Prerequisites

Make sure you have git and Docker desktop installed.

Installation

zpm “install dataset-finance”

Development

Clone/git pull the repo into any local directory

$ git clone https://github.com/oliverwilms/dataset-finance.git

Open the terminal in this directory and run:

$ docker-compose up -d

How to Test it

In IRIS terminal:

$ docker-compose exec iris iris session iris

Node: 01c3974cc7dc, Instance: IRIS

USER>s x = ##class(dc.data.util).UpdateDateValues()
Begin UpdateDateValues
............................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................UpdateDateValues Done

USER>do $System.SQL.Shell()
SQL Command Line Shell

The command prefix is currently set to: <<nothing>>.
Enter <command>, 'q' to quit, '?' for help.
[SQL]USER>>LOAD DATA FROM FILE '/opt/irisbuild/data/finance.csv' INTO dc_data_finance.transact(TranDate,CheckNumber,Merchant,Category,SubCategory,Memo,Credit,Debit,BillPay,DebitCard,Account,Status) VALUES("Date","Check",Merchant,Category,SubCategory,Memo,Credit,Debit,"Bill Pay","Debit Card",Account,Status) USING {"from":{"file":{"header":"1"}}}

  1.  LOAD DATA FROM FILE '/opt/irisbuild/data/finance.csv' INTO dc_data_finance.transact(TranDate,CheckNumber,Merchant,Category,SubCategory,Memo,Credit,Debit,BillPay,DebitCard,Account,Status) VALUES(&quot;Date&quot;,&quot;Check&quot;,Merchant,Category,SubCategory,Memo,Credit,Debit,&quot;Bill Pay&quot;,&quot;Debit Card&quot;,Account,Status) USING {&quot;from&quot;:{&quot;file&quot;:{&quot;header&quot;:&quot;1&quot;}}}
    

2007 Rows Affected
statement prepare time(s)/globals/cmds/disk: 0.0255s/2,431/27,070/1ms
execute time(s)/globals/cmds/disk: 2.7615s/107,078/950,441/18ms
cached query class: %sqlcq.USER.cls6

[SQL]USER>>

In InterSystems SQL Tools in VSCode

Open repo in VSCode (see develoment above)
Install InterSystems SQLTools

Use the connection “iris-dataset-titanic”

Open dc.data.Titanic table and see the records:
Screenshot 2021-01-21 at 13 33 53

How to start coding

This repository is ready to code in VSCode with ObjectScript plugin.
Install VSCode, Docker and ObjectScript plugin and open the folder in VSCode.
Open /src/cls/PackageSample/ObjectScript.cls class and try to make changes - it will be compiled in running IRIS docker container.
docker_compose

Feel free to delete PackageSample folder and place your ObjectScript classes in a form
/src/Package/Classname.cls
Read more about folder setup for InterSystems ObjectScript

Made with
Install
zpm install dataset-finance download archive
Version
1.0.815 Jan, 2022
ObjectScript quality test
Category
Technology Example
Works with
InterSystems IRIS
First published
10 Jan, 2022
Last checked by moderator
27 Jun, 2023Works