Mapping of column and date treatment

[Jwwwson]

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

[Per]

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!

[Jwwwson]

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

[Per · Accepted solution]

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