Get Latest 5 Records

[#1]

Dear All,

Need help in getting Latest 5 Records.

I have table with data something like below.

LeadID	Notes	Date	Uniqueid
1	Site Visit	01-Mar-2017	1
2	Agreed	01-Mar-2017	2
3	Agreed	01-Mar-2017	3
1	Test	02-Mar-2017	4
1	Test2	03-Mar-2017	5
1	Test3	04-Mar-2017	6
1	Test4	05-Mar-2017	7
1	Test5	06-Mar-2017	8
1	Test6	07-Mar-2017	9
2	Site	10-Mar-2017	10
2	Site	11-Mar-2017	11
3	Agreed	05-May-2017	12
3	Agreed	10-May-2017	13

I want the latest 5 Records Output will be something like below.

LeadID	Notes	Date	Uniqueid
1	Test6	07-Mar-2017	9
1	Test5	06-Mar-2017	8
1	Test4	05-Mar-2017	7
1	Test3	04-Mar-2017	6
1	Test2	03-Mar-2017	5
2	Site	11-Mar-2017	11
2	Site	10-Mar-2017	10
2	Agreed	01-Mar-2017	2
3	Agreed	10-May-2017	13
3	Agreed	05-May-2017	12
3	Initiated	01-Mar-2017	3

Kindly help me with this.

Regards,

Keerthi KS

[qlikview979]

Hi keerti,

do like this

T1:

LOAD * Inline [

LeadID, Notes, Date, Uniqueid

1, Site Visit, 01-Mar-2017, 1

2 ,  Agreed ,01-Mar-2017 ,2

3 ,Agreed ,01-Mar-2017 ,3

1 ,Test ,02-Mar-2017 ,4

1 ,Test2 ,03-Mar-2017 ,5

1 ,Test3 ,04-Mar-2017 ,6

1 ,Test4 ,05-Mar-2017 ,7

1 ,Test5 ,06-Mar-2017 ,8

1 ,Test6 ,07-Mar-2017 ,9

2 ,Site ,10-Mar-2017 ,10

2 ,Site ,11-Mar-2017 ,11

3 ,Agreed ,05-May-2017 ,12

3 ,Agreed ,10-May-2017 ,13

];

NoConcatenate

T2:

First 5

LOAD LeadID,

Notes,

Date,

Uniqueid

Resident T1    Order by Uniqueid  desc ;

DROP Table T1;

[qlikview979]

As Per Uniqueid

latest 5 result

[qlikview979]

If you want latest 10, replace 10 instead 5 in second(T2) table.

[#5]

Dear Mahesh,

I need latest 5 Records for each LeadID. I have to group by LeadID. Each leadID should have latest 5 records.

Please let me know if you need any other details.

Regards,

Keerthi KS

[Digvijay_Singh]

Check this -

[Kushal_Chawda]

try this

T1:
LOAD *,Date#(Date,'DD-MMM-YYYY') as DATE Inline [
LeadID, Notes, Date, Uniqueid
1, Site Visit, 01-Mar-2017, 1
2 ,  Agreed ,01-Mar-2017 ,2
3 ,Agreed ,01-Mar-2017 ,3
1 ,Test ,02-Mar-2017 ,4
1 ,Test2 ,03-Mar-2017 ,5
1 ,Test3 ,04-Mar-2017 ,6
1 ,Test4 ,05-Mar-2017 ,7
1 ,Test5 ,06-Mar-2017 ,8
1 ,Test6 ,07-Mar-2017 ,9
2 ,Site ,10-Mar-2017 ,10
2 ,Site ,11-Mar-2017 ,11
3 ,Agreed ,05-May-2017 ,12
3 ,Agreed ,10-May-2017 ,13
];


T2:
NoConcatenate
LOAD *,
     AutoNumber(DATE,LeadID) as Sequence
Resident T1
Order by LeadID,DATE desc;

DROP Table T1;

Final:
NoConcatenate
LOAD *
Resident T2
where Sequence<=5;


DROP Table T2;

[jagan]

Hi Keerthi,

Try like this

Script: Sample Data

Temp:

LOAD * Inline [

LeadID, Notes, Date, Uniqueid

1, Site Visit, 01-Mar-2017, 1

2 ,  Agreed ,01-Mar-2017 ,2

3 ,Agreed ,01-Mar-2017 ,3

1 ,Test ,02-Mar-2017 ,4

1 ,Test2 ,03-Mar-2017 ,5

1 ,Test3 ,04-Mar-2017 ,6

1 ,Test4 ,05-Mar-2017 ,7

1 ,Test5 ,06-Mar-2017 ,8

1 ,Test6 ,07-Mar-2017 ,9

2 ,Site ,10-Mar-2017 ,10

2 ,Site ,11-Mar-2017 ,11

3 ,Agreed ,05-May-2017 ,12

3 ,Agreed ,10-May-2017 ,13

];

Final:

NoConcatenate

LOAD

*,

If(Peek(LeadID) = LeadID, Peek(Order) + 1, 1) AS Order

RESIDENT Temp

ORDER BY LeadID, Date DESC;

DROP TABLE Temp;

Straight Table:

Dimension: LeadID, Notes, Date, UniqueID

Expression: =Only({<Order={'<=5'}>}Order)

Please find attached file for solution.

Regards,

jagan.