Stitching files

Question

Hi Community,

I have to stitch certain files from SFTP. SFTP contains daily files.

Each file has 90 days of data in it, so date ranges that we wish to pull outside of 90 days require multiple files. The issue with this is that the files are most accurate with days further in the past (i.e. we could be missing data points if we were to simple stitch files every 90 days). To fix this issue, we should be stitching the files together in a way that prioritizes the last 30 days of each file. Below is an example.

Date Range Pull: 1/1/2021 – 6/1/2021 = 151 days

Files used:

*       6/1/2021

* o  Most recent date for the range pull should dictate which file we start with

*       5/2/2021

* o  30 days prior to 6/1/2021

*       4/1/2021

* o  31 days prior to 5/2/2021
* o  The reason this one is not 30 days is because the number of days between 1/1/2021 and 6/1/2021 is not divisible by 30, so instead of using another file to take the other day, we would be fine with using 4/1/2021 to use the 31st day in the file.
* o  The last file can leverage the last 30-60 days in the file, but if the number of days exceeds 60, we must pull a new file.

How the files will be stitched together:

*       6/1/2021

* o Uses data from 3/3/2021 – 6/1/2021

*       5/2/2021

* o Uses data from 2/1/2021 – 3/2/2021

*       4/1/2021

* o Uses data from 1/1/2021 – 2/1/2021

DawnDuong · Answer

If the number of files is dynamic, you can try a batch/iterative  + dropdown interface.

The interface is to allow you to select the files that you want to process, then feed that list into a macro that runs through the list.

karan1107 · Answer

Hi, I have used a date interface tool for the start date and end date.. It's not like i'll always have 3 files, that will depend upon the range .. greater the range, more the files.

DawnDuong · Answer

hi @karan1107

Seems to me that you need a dynamic solution that allows you to specify the file as well as the range of dates you want to from each file and that stitching the data together after the range/file has been correctly set is not an issue.

You can try building an app with the following inputs:

1) File browse x 3 (since you need a max of 3 files only)

2) 6 drop down tools, 2 for each file, to specify the start/end dates.

3) create 3 groups, each group has 1 file browse and 2 drop down tools

For each of the files you select, add a filter tool which filters in the desired date range based on the inputs from the drop down tools.

Then you stick the 3 data together.

Hope this helps.
Dawn.