Hello. Hope you all are ok.
I am trying to transform my data but not able to progress. Here is the problem.
Have a table with the following columns
Can you please help me?
Thanks
| DESCRIPTION | DATE | SALARY |
| FINISH 1 | 10/30/2017 | 100 |
| START 1 | 10/18/2016 | 100 |
| FINISH 2 | 6/11/2003 | 1091.25 |
| MODIFICATION 1 | 1/1/2003 | 1091.25 |
| MODIFICATION 2 | 12/1/2002 | 1091.25 |
| MODIFICATION 3 | 11/1/2002 | 1634.14 |
| MODIFICATION 4 | 10/1/2002 | 1636.09 |
| MODIFICATION 5 | 9/1/2002 | 1634.14 |
| MODIFICATION 6 | 6/1/2002 | 1634.49 |
| MODIFICATION 7 | 5/1/2002 | 1728.61 |
| MODIFICATION 8 | 4/1/2002 | 1367.06 |
| MODIFICATION 9 | 3/1/2002 | 1361.9 |
| MODIFICATION 10 | 2/1/2002 | 1366.79 |
| MODIFICATION 11 | 1/1/2002 | 1053.75 |
| MODIFICATION 12 | 6/16/2001 | 1008.75 |
| MODIFICATION 13 | 1/1/2001 | 1008.75 |
| MODIFICATION 14 | 8/1/2000 | 947 |
| MODIFICATION 15 | 1/1/2000 | 947.5 |
| MODIFICATION 16 | 12/3/1998 | 861.25 |
| MODIFICATION 17 | 1/1/1998 | 755 |
| MODIFICATION 18 | 4/1/1997 | 661.25 |
| MODIFICATION 19 | 4/1/1996 | 557.41 |
| MODIFICATION 20 | 12/4/1995 | 484.88 |
| MODIFICATION 21 | 6/1/1995 | 457.5 |
| MODIFICATION 22 | 4/1/1995 | 448.98 |
| MODIFICATION 23 | 1/1/1995 | 408.5 |
| MODIFICATION 24 | 1/1/1994 | 381.75 |
| MODIFICATION 25 | 7/21/1993 | 256.86 |
| MODIFICATION 26 | 1/1/1993 | 142.7 |
| START 3 | 9/28/1992 | 133.3 |
| FINISH 4 | 11/11/1991 | 119 |
| MODIFICATION 27 | 11/16/1990 | 119 |
| FINISH 5 | 10/19/1990 | 100.8 |
and I need the following:
| DESCRIPTION | START DATE | FINISH DATE | SALARY |
| FINISH 1 | 10/18/2016 | 10/30/2017 | 100 |
| MODIFICATION 1 | 1/1/2003 | 6/11/2003 | 1091.3 |
| MODIFICATION 2 | 12/1/2002 | 1/1/2003 | 1091.3 |
| MODIFICATION 3 | 11/1/2002 | 12/1/2002 | 1634.1 |
| MODIFICATION 4 | 10/1/2002 | 11/1/2002 | 1636.1 |
| MODIFICATION 5 | 9/1/2002 | 10/1/2002 | 1634.1 |
| MODIFICATION 6 | 6/1/2002 | 9/1/2002 | 1634.5 |
| MODIFICATION 7 | 5/1/2002 | 6/1/2002 | 1728.6 |
| MODIFICATION 8 | 4/1/2002 | 5/1/2002 | 1367.1 |
| MODIFICATION 9 | 3/1/2002 | 4/1/2002 | 1361.9 |
| MODIFICATION 10 | 2/1/2002 | 3/1/2002 | 1366.8 |
| MODIFICATION 11 | 1/1/2002 | 2/1/2002 | 1053.8 |
| MODIFICATION 12 | 6/16/2001 | 1/1/2002 | 1008.8 |
| MODIFICATION 13 | 1/1/2001 | 6/16/2001 | 1008.8 |
| MODIFICATION 14 | 8/1/2000 | 1/1/2001 | 947 |
| MODIFICATION 15 | 1/1/2000 | 8/1/2000 | 947.5 |
| MODIFICATION 16 | 12/3/1998 | 1/1/2000 | 861.25 |
| MODIFICATION 17 | 1/1/1998 | 12/3/1998 | 755 |
| MODIFICATION 18 | 4/1/1997 | 1/1/1998 | 661.25 |
| MODIFICATION 19 | 4/1/1996 | 4/1/1997 | 557.41 |
| MODIFICATION 20 | 12/4/1995 | 4/1/1996 | 484.88 |
| MODIFICATION 21 | 6/1/1995 | 12/4/1995 | 457.5 |
| MODIFICATION 22 | 4/1/1995 | 6/1/1995 | 448.98 |
| MODIFICATION 23 | 1/1/1995 | 4/1/1995 | 408.5 |
| MODIFICATION 24 | 1/1/1994 | 1/1/1995 | 381.75 |
Hi Victor,
You have:
MODIFICATION 1- Start Date: 1/1/2003 , Salary: 1091.25
In your desired output, from where is Finish Date coming from, not seeing it in the initial data?
| DESCRIPTION | START DATE | FINISH DATE | SALARY |
| MODIFICATION 1 | 1/1/2003 | 6/11/2003 | 1091.3 |
Hi Catalin, the start date is formed by the previous date:
Example:
from below table,
Start 1 = 10/18/2016
Modification 1 = Start date 1/1/2003 and finish date 6/11/2003
Modification 2 = start date 12/1/2002 and finish date 1/1/2003
Hope this helps
Thanks in advance
| DESCRIPTION | DATE | SALARY |
| FINISH 1 | 10/30/2017 | 100 |
| START 1 | 10/18/2016 | 100 |
| FINISH 2 | 6/11/2003 | 1091.25 |
| MODIFICATION 1 | 1/1/2003 | 1091.25 |
| MODIFICATION 2 | 12/1/2002 | 1091.25 |
Ok, so Modification 1 has the finish date of FINISH 2 ?! What's the order of the operations, as it's not as obvious as you may think. The date column looks like it is a start date or an end date, depending on description, correct?
But, why do you have in your sample data (first post) START 1 and 3, FINISH 1, 2, 4 and 5 and MODIFICATION 1 to 27. Why there are gaps and which ones are related and must be analyzed as a group?
Please provide the entire logical chain, we can't imagine our own process, guessing always leads to wasted time.
Thanks for understanding, we'll try to help you.
