Identify Added and Terminated records

[BI_Dev]

How to identify added and terminated records.

ID            Period   

401         12/2/2024       

401         1/01/2025

401         2/01/2025

401         3/01/2025         

386        12/2/2024          

386        1/01/2025         

386         2/01/2025

386        3/01/2025

Now I also want to create 'Added/Terminated' column where I can identify when the record is added and Terminated.
Here 401 is added on 03/1/25 and 386 is added in 12/2/24 and stayed in 01/01/25 and got terminated in 02/01/25

ID            Date               Status             Added/Terminated

401         12/2/2024       

401         1/01/2025

401         2/01/2025

401         3/01/2025         1                   Added

386        12/2/2024          1                   Added

386        1/01/2025          1                   No Change

386         2/01/2025                              Terminated

386        3/01/2025

Below is the script I tried but it is not giving the correct result.

Variance_Tmp:
LOAD * Inline [

ID, Period
401,
401,
401,
401, 3/01/2025
386, 12/2/2024
386, 1/01/2025
386, 2/01/2025
386,

];

raw:
LOAD
ID as ID,
ID as Key,

date(Period,'MMM - YYYY') as Snapshot,
'1' as flag

Resident Variance_Tmp;
Drop table [Variance_Tmp];

tempAsOf:
Load

Key,
Key as ID_2;

LOAD fieldvalue('Key',iterno()) as Key
AUTOGENERATE 1
WHILE len(fieldvalue('Key',iterno()));

left join(tempAsOf)

LOAD date(fieldvalue('Snapshot',iterno())) as Period
AUTOGENERATE 1
WHILE len(fieldvalue('Snapshot',iterno()));

Left Join (tempAsOf)
Load

Key,
Snapshot as Period,
flag as status

Resident raw;

AsOF:
Load
*
,If(ID_2 = Peek(ID_2) AND isnull(status), 'Terminated', 'Added') AS [Added/Terminated]

//,if(ID_2=Peek(ID_2) and isnull(check),'Terminated','Added') as [Added/Terminated]
//,if(isnull(check),'Terminated','Added') as [Added/Terminated]

Resident tempAsOf
Order by Key,Period;
drop table tempAsOf;

Any help is appreciated.Thank you.

@rwunderlich @Lech_Miszkiewicz @vinieme12 @igoralcantara @amonjaras_c40 

[igoralcantara]

Try something like this:

// Step 1: Load the base data
BaseData:
LOAD
ID,
Date(Date#(Period, 'M/D/YYYY')) as Period // Make sure Period is a date
INLINE [
ID, Period
401, 12/2/2024
401, 1/01/2025
401, 2/01/2025
401, 3/01/2025
386, 12/2/2024
386, 1/01/2025
386, 2/01/2025
386, 3/01/2025
];

// Step 2: Create a mapping of first and last Periods per ID
LEFT JOIN (BaseData)
LOAD
ID,
Min(Period) as FirstPeriod,
Max(Period) as LastPeriod
RESIDENT BaseData
GROUP BY ID;

// Step 3: Final load with status logic
FinalData:
LOAD
ID,
Period,
IF(Period = FirstPeriod, 'Added',
IF(Period = LastPeriod, 'Terminated', '')) as Status
RESIDENT BaseData;

// Optional cleanup
DROP TABLE BaseData;

[amonjaras_c40]

Hello!

Are you not missing something in your data? How can you tell that ID 306 stayed on on 1/01/2025, but was terminated on 2/01/2025 just by looking at two columns?:

386, 1/01/2025
386, 2/01/2025

[BI_Dev]

Thank you but that did not work.

[BI_Dev]

Yeah - I have created the status column based on the Period and that tells me when the record is active.

LOAD * Inline [

ID, Period
401,
401,
401,
401, 3/01/2025
386, 12/2/2024
386, 1/01/2025
386, 2/01/2025
386,

];