Loop-Question

[Ronein]

Hello,

I would like to do the following task.

I have one  data set with transcations of purcahses in a shop with columns: ID,date,amount. (name of data set is Trans_tbl)

I have multiple CSV files called tYYYYMMDD with following columns: ID,date,balance

Please note that for each business day there should be a CSV file.

The task is :

1-Create a data set with information of business days

It will be done by taking distinct dates from Trans_tbl

2-Create a Loop that do the following"

A-Select transcations  that were done in date X (From data set Trans_tbl)

B-Import CSV File in date X 

C-Import CSV File in date X-1

D-Merge(Left Join) data set Trans_tbl with CSV File in date X (To  get column of balance in date X)

E-Merge(Left Join) data set created in step D with CSV File in date X-1 (To  get column of balance in date X-1)

Please note that date X-1 is one business day before date X 

F-Delete data sets :CSV File in date X,CSV File in date X-1 (Because they are very big data sets )

Any ideas how to do it?

[Kurt_Bremser]

Over which range of dates do you intend to run the loop?

[Ronein]

Hello, From 01.01.2020 till today

[Kurt_Bremser]

And how large is trans_tbl?

[Ronein]

Trans_tbl is 200,000 rows 

[Kurt_Bremser]

Then you should positively read all csv files in one data step, and load trans_tbl into a hash object for filtering.