End Date out of Start Date employees table

[chloe]

Hey,

I want to create a new column as end date out of the start date column as below:

SalesmanID  |  Department          |     StartDate  |  EndDate

4750                 |   Management       |    4/30/2020 |           -

4750                 |  Ladies' Footwea  | 01/07/2019 |    04/29/2020

How can I do it? 

Thanks!

[JustinDallas · Accepted solution]

The key to your answer will be using the Previous keyword along with ordering your table.  I have a sample script below

DATA:
LOAD *,
Date(Date#(StartDateString, 'MM/DD/YYYY')) AS StartDate
;
LOAD * Inline
[
	SalesmanID, Department, StartDateString
    4750, Management, 4/30/2020, 
    4750, Diesel Engine Spark Plugs, 02/05/2018,
    4750, Ladies' Footwear, 01/07/2019,
    4750, Pet Rocks, 08/09/2018, 
    123, Management, 5/30/2020, 
    123, Hypervisor Sales, 12/05/2018,
    123, Microphone Sales, 01/23/2019,
    123, HR, 08/09/2017, 
]
;

DROP FIELD StartDateString
;

DATA2:
NoConcatenate
LOAD * RESIDENT DATA
ORDER BY SalesmanID, StartDate DESC
;

DROP TABLE DATA
;

DATA3:
LOAD 
*,
If(Previous(SalesmanID) = SalesmanID, Date(Previous(StartDate) - 1, Null())) AS 'EndDate'
RESIDENT DATA2
;

DROP TABLE DATA2
;

EXIT Script
;

[chloe]

Detailed and excellent response. thank you! 😀

[JustinDallas]

Feel free to mark it correct if it works out for you.

[Jamshaid_Afzal]

using previous make the first row null, what if we don't want it null

[JustinDallas]

Then you should put something else in the if-statement.  Right now, it's got Null(), you could put anything that you wanted there.

[Jamshaid_Afzal]

thanks for your response, but I want 04/29/2020 in front of the Management department