Hi,
I have the following data set and I would like to process them to become the desired results. It includes two steps, first is to match the original staff with the transfer list and indicates a change of staff. Second is to determine the date of transfer and allocate the hours in the corresponding months in accordance to the new list.
I have encountered an issue, since the date column will change every month when I have a new data source, I need to change the formula in the tools of workflow. I would like to design a workflow that I can use without changing it every month. I once thought of using dynamic rename to change the name of the date column to ' 1st month', '2nd month' etc. However, in that case I cannot match with the transfer date.
Kindly advise if you have any idea how to deal with the date issue. Thanks!
Cheers,
Jason
Source data 1:
Name | Job reference | Previous staff | 31-Jul-20 | 31-Aug-20 | 30-Sep-20 | 31-Oct-20 | 30 November 2020 | 31-Dec-20 | 31-Jan-21 | 28-Feb-21 | 31-Mar-21 | |
1 | Alex | A001 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | |
2 | Philip | A002 | 20 | 20 | 20 | 20 | 20 | 20 | 20 | 20 | 20 | |
3 | Albert | A003 | 30 | 30 | 30 | 30 | 30 | 30 | 30 | 30 | 30 |
Source data 2 (transfer list and date):
Original staff | Replacement | Job reference | Transfer date | |
1 | Alex | Peter | A001 | 31-Jul-20 |
2 | Philip | Mary | A002 | 31-Jan-21 |
3 | Albert | Lily | A003 | 30-Sep-20 |
Desired Output:
Name | Job reference | Previous staff | 31-Jul-20 | 31-Aug-20 | 30-Sep-20 | 31-Oct-20 | 30 November 2020 | 31-Dec-20 | 31-Jan-21 | 28-Feb-21 | 31-Mar-21 | REMARKS | |
1 | Peter | A001 | Alex | 0 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | 10 | |
2 | Mary | A002 | Philip | 20 | 20 | 20 | 20 | 20 | 20 | 20 | 20 | 20 | |
3 | Lily | A003 | Albert | 30 | 30 | 30 | 30 | 30 | 30 | 30 | 30 | 30 | |
4 | Alex | A001 | 10 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | Transferred | |
5 | Philip | A002 | 20 | 20 | 20 | 20 | 20 | 20 | 20 | 0 | 0 | Transferred | |
6 | Albert | A003 | 30 | 30 | 30 | 0 | 0 | 0 | 0 | 0 | 0 | Transferred |
Thanks & regards,
Jason
Solved! Go to Solution.
Hi,
If you use the Transpose tool on Source data 1, you can get all the existing and new dates in one single date field like below. I believe that will solve the issue? Important to keep the Dynamic or Unknown Columns checked for new dates coming in
Hope that helps!
Hi @Per,
Thanks for your reply!
I am afraid that I was making my issue clear. Attached please find my workflow.
I did not achieve what I expected, i.e. included the previous staff name it the previous staff column, and allocation of hours according to the transfer date.
Kindly advise what amendment shall I take. Thanks!
Cheers,
Jason
Hi @Jwwwson
No, sorry for ME not being clear 🙂🙂
I understood your issue just fine, but I only answered part of the question, namely how you could re-structure the data to create a solution.
But now I believe I have the entire solution. Your dummy data did not match the original data completely and you might need to rename as appropriate, but please see if this works for you