Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

3 months rolling data into YTD data

Biko
7 - Meteor

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 IDDateRegionStatus
111Apr-15AAAFail
222Apr-17BBBFail
333Apr-20CCCSuccess

 

May 2nd file:

Unique IDDateRegionStatus
111Apr-15AAAFail
222Apr-17BBBFail
333Apr-20CCCSuccess
444*Apr-21CCCSuccess

 

May 3rd file:

Unique IDDateRegionStatus
111Apr-15AAASuccess**
222Apr-17BBBSuccess**
333Apr-20CCCSuccess
444Apr-21CCCSuccess
555*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.

5 REPLIES 5
fmvizcaino
17 - Castor
17 - Castor

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.

 

fmvizcaino_2-1589292334036.png

 

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

 

 

 

Biko
7 - Meteor

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. 

 

fmvizcaino
17 - Castor
17 - Castor

Hi @Biko ,

 

The workflow already get the newest row of each unique ID.

Would you like to check and fill something in your final dataset? It is possible to check previous status, but I need to know what you would like to do with this check.

 

Best,

Fernando Vizcaino

 

Biko
7 - Meteor

I would like to get a daily volumes and its statuses snapshot daily for dashboard/kpi purposes. Ideally I would like to get a YTD data daily so I can refresh my data.

 

So for my dashboard report I would have YTD report as below:

May 1st (1st file): 3 transactions and 66% fail 33% successful 

May 2nd (2nd file): 4 transactions and 50% fail 50% succesful

May 3rd (3rd file): 5 transactions and 100% successful

 

From your workflow I would get the additional transactions but not the status update:

May 1st (1st file): 3 transactions and 66% fail 33% successful 

May 2nd (2nd file): 4 transactions and 50% fail 50% succesful

May 3rd (3rd file): 5 transactions and 40% fail 60% successful

 

 

Does this make sense? Thanks again for your help.

fmvizcaino
17 - Castor
17 - Castor

Hi @Biko ,

 

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

I also inserted a calculation of the statuses.

 

Best,

Fernando Vizcaino

Labels