Hi All, New joiner in using Alteryx. I have a daily csv file that contains 3 months worth of data. I would like to stitch them all up together to create a master file. In addition, I want the workflow to check on the latest file if there were any differences from the last one, and if there are to update with the most current one. May 1st file: Unique IDDateRegionStatus111Apr-15AAAFail222Apr-17BBBFail333Apr-20CCCSuccess May 2nd file: Unique IDDateRegionStatus111Apr-15AAAFail222Apr-17BBBFail333Apr-20CCCSuccess444*Apr-21CCCSuccess May 3rd file: Unique IDDateRegionStatus111Apr-15AAASuccess**222Apr-17BBBSuccess**333Apr-20CCCSuccess444Apr-21CCCSuccess555*May-1AAASuccess *new addition unique ID ref for that day's file **status update on previous unique ID ref The goal of this exercise is because my IT dept can't export out a YTD data to me on a daily basis. So I want to make my own YTD data. I understand after 3 months, there is no more status "refresh" but this is the best that I can live with. How would you suggest doing this? Thanks all.

3 months rolling data into YTD data

Hi All,

New joiner in using Alteryx. I have a daily csv file that contains 3 months worth of data. I would like to stitch them all up together to create a master file. In addition, I want the workflow to check on the latest file if there were any differences from the last one, and if there are to update with the most current one.

May 1st file:

Unique ID	Date	Region	Status
111	Apr-15	AAA	Fail
222	Apr-17	BBB	Fail
333	Apr-20	CCC	Success

May 2nd file:

Unique ID	Date	Region	Status
111	Apr-15	AAA	Fail
222	Apr-17	BBB	Fail
333	Apr-20	CCC	Success
444*	Apr-21	CCC	Success

May 3rd file:

Unique ID	Date	Region	Status
111	Apr-15	AAA	Success**
222	Apr-17	BBB	Success**
333	Apr-20	CCC	Success
444	Apr-21	CCC	Success
555*	May-1	AAA	Success

*new addition unique ID ref for that day's file

**status update on previous unique ID ref

The goal of this exercise is because my IT dept can't export out a YTD data to me on a daily basis. So I want to make my own YTD data. I understand after 3 months, there is no more status "refresh" but this is the best that I can live with. How would you suggest doing this?

Thanks all.

Datasets

Join

Accepted answers

fmvizcaino

Hi @Biko ,

I noticed that I've shared the incomplete workflow with you.

I also inserted a calculation of the statuses.

Best,

Fernando Vizcaino

2020-05-12 3 months rolling data into YTD data.yxmd

All comments

fmvizcaino

Hi @Biko ,

you can solve that by adding a sample tool in your workflow to select the last possible data for each of the uniqueIDs.

1. .\2020-??-?? file?.xlsx - I'm using this file name to get all file names with that structure, so all excel files from 2020 that follows this format will be loaded and concatenated together.

2. Sorting the data to guarantee the files are sorted as it should.

3. I'm using a sample tool where I selected to only get the last row of data for each unique ID

Example attached as well. Sharing in a zip format for you not to have possible version problems.

Best,

Fernando Vizcaino

2020-05-12 3 months rolling data into YTD data.zip

Biko

Thanks Fernando. That would help into adding more unique ID added into the data. Is there anyway it would check the status of previous data as well? For example, ref 111 and 222 were fail in the first two files and then. updated to successful in the third file.

Quick Links

This months top contributors

FláviaB 262

JORGE4900 235

caltang 177

DanM 167

IraWatt 157