topic Re: End Date out of Start Date employees table in App Development

End Date out of Start Date employees table

chloe — Thu, 11 Feb 2021 14:05:30 GMT

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!

End Date out of Start Date employees table

JustinDallas — Thu, 11 Feb 2021 14:26:14 GMT

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 ;

Re: End Date out of Start Date employees table

chloe — Thu, 11 Feb 2021 15:08:10 GMT

Detailed and excellent response. thank you! 😀

Re: End Date out of Start Date employees table

JustinDallas — Thu, 11 Feb 2021 15:30:21 GMT

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

Re: End Date out of Start Date employees table

Jamshaid_Afzal — Thu, 06 Oct 2022 07:36:40 GMT

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

Re: End Date out of Start Date employees table

JustinDallas — Sat, 08 Oct 2022 01:51:24 GMT

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

Re: End Date out of Start Date employees table

Jamshaid_Afzal — Mon, 10 Oct 2022 10:18:07 GMT

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