topic Identify Added and Terminated records in App Development

Identify Added and Terminated records

BI_Dev — Tue, 08 Apr 2025 13:19:01 GMT

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

Re: Identify Added and Terminated records

igoralcantara — Tue, 08 Apr 2025 13:55:40 GMT

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;

Re: Identify Added and Terminated records

amonjaras_c40 — Tue, 08 Apr 2025 14:44:23 GMT

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

Re: Identify Added and Terminated records

BI_Dev — Tue, 08 Apr 2025 15:35:04 GMT

Thank you but that did not work.

Re: Identify Added and Terminated records

BI_Dev — Wed, 09 Apr 2025 12:50:09 GMT

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,

];