Home Applications dataset-finance

dataset-finance

InterSystems does not provide technical support for this project. Please contact its developer for the technical assistance.
5
1 reviews
0
Awards
239
Views
20
IPM installs
0
0
Details
Releases (9)
Reviews (1)
Issues
Articles (1)
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 edited
15 Jan, 2022
Last checked by moderator
17 Sep, 2024Works