Identifying First and Last Value of each ID within Script

[aetingu12]

Dear all,

I have a table with a few columns.  What I need to do is to group and identify the first and the last record for each id.  I have managed to identify the first one, but getting the last one is proving to be complicated.  

Date	ID	Origination(Expected)
Jan	23	First
Feb	23	-
Mar	23	Last
Jan	45	First
Feb	45	-
Mar	45	-
Apr	45	Last

And here is my code for the First:

Testing:
LOAD * INLINE [
    Date, ID, Owed, Status, Movement
    31/12/2018,23, 0, , 0
    31/12/2018, 45, 10, , 0
    31/01/2019, 23, 0, , 0
    31/01/2019, 45, 10, , 0
    28/02/2019, 23, 0, , 0
    28/02/2019, 45, 10, , 0
    31/03/2019, 23, 10, New, 10
    31/03/2019, 45, 15, Increase, 5
    30/04/2019, 23, 10, , 0
    30/04/2019, 45, 10, Decrease, -5
    31/05/2019, 23, 15, Increase, 5
    31/05/2019, 45, 10, , 0
    30/06/2019, 23, 10, Decrease, -5
    30/06/2019, 45, 12, Increase, 2
    31/07/2019, 23, 10, , 0
    31/07/2019, 45, 10, Decrease, -2
    31/08/2019, 23, 15, Increase, 5
    31/08/2019, 45, 10, , 0
    30/09/2019, 23, 14, Decrease, -1
    30/09/2019, 45, 0, Paid off, -10
    31/10/2019, 23, 10, Decrease, -4
    31/10/2019, 45, 0, , 0
    30/11/2019, 23, 0, Paid off, -10
    30/11/2019, 45, 0, , 0
    31/12/2019, 23, 0, , 0
    31/12/2019, 45, 0, , 0
];
NoConcatenate
Sorted_Data:
LOAD *,
if(ID = Peek(ID),'Old','New') as "Origination"
Resident Testing
Order by ID, Date;
DROP Table Testing;

I just can not get the Last identified.

Any help is more than appreciated 

[sunny_talwar · Accepted solution]

You can add another load where you sort descending on Date

Testing:
LOAD * INLINE [
    Date, ID, Owed, Status, Movement
    31/12/2018,23, 0, , 0
    31/12/2018, 45, 10, , 0
    31/01/2019, 23, 0, , 0
    31/01/2019, 45, 10, , 0
    28/02/2019, 23, 0, , 0
    28/02/2019, 45, 10, , 0
    31/03/2019, 23, 10, New, 10
    31/03/2019, 45, 15, Increase, 5
    30/04/2019, 23, 10, , 0
    30/04/2019, 45, 10, Decrease, -5
    31/05/2019, 23, 15, Increase, 5
    31/05/2019, 45, 10, , 0
    30/06/2019, 23, 10, Decrease, -5
    30/06/2019, 45, 12, Increase, 2
    31/07/2019, 23, 10, , 0
    31/07/2019, 45, 10, Decrease, -2
    31/08/2019, 23, 15, Increase, 5
    31/08/2019, 45, 10, , 0
    30/09/2019, 23, 14, Decrease, -1
    30/09/2019, 45, 0, Paid off, -10
    31/10/2019, 23, 10, Decrease, -4
    31/10/2019, 45, 0, , 0
    30/11/2019, 23, 0, Paid off, -10
    30/11/2019, 45, 0, , 0
    31/12/2019, 23, 0, , 0
    31/12/2019, 45, 0, , 0
];

Sorted_Data_Temp1:
LOAD *,
	 if(ID = Peek(ID), Null(), 'Last') as "Origination_Temp1"
Resident Testing
Order by ID, Date desc;

Sorted_Data_Temp2:
LOAD *,
	 if(ID = Peek(ID), Null(), 'First') as "Origination_Temp2"
Resident Sorted_Data_Temp1
Order by ID, Date;

Sorted_Data:
LOAD Date,
	 ID,
	 Owed,
	 Status,
	 Movement,
	 If(Len(Trim(Origination_Temp2)) > 0, Origination_Temp2,
	 	If(Len(Trim(Origination_Temp1)) > 0, Origination_Temp1)) as "Origination"
Resident Sorted_Data_Temp2;

DROP Table Testing, Sorted_Data_Temp1, Sorted_Data_Temp2;

[aetingu12]

Thank you very much as always!