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.
Solved! Go to Solution.
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
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.
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
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.
Hi @Biko ,
I noticed that I've shared the incomplete workflow with you.
I also inserted a calculation of the statuses.
Best,
Fernando Vizcaino