topic Re: Identifying First and Last Value of each ID within Script in QlikView

Identifying First and Last Value of each ID within Script

aetingu12 — Sat, 16 Nov 2024 01:43:21 GMT

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

Re: Identifying First and Last Value of each ID within Script

sunny_talwar — Wed, 11 Dec 2019 14:01:35 GMT

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;

Re: Identifying First and Last Value of each ID within Script

aetingu12 — Wed, 11 Dec 2019 14:23:16 GMT

Thank you very much as always!