Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
aetingu12
Creator
Creator

Identifying First and Last Value of each ID within Script

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.  

DateIDOrigination(Expected)
Jan23First
Feb23-
Mar23Last
Jan45First
Feb45-
Mar45-
Apr45Last

 

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 

Labels (4)
1 Solution

Accepted Solutions
sunny_talwar

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;

 

View solution in original post

2 Replies
sunny_talwar

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
Creator
Creator
Author

Thank you very much as always!