topic Re: Get Latest 5 Records in QlikView

Get Latest 5 Records

Tue, 23 May 2017 09:52:44 GMT

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

Re: Get Latest 5 Records

qlikview979 — Tue, 23 May 2017 10:01:25 GMT

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;

Re: Get Latest 5 Records

qlikview979 — Tue, 23 May 2017 10:02:59 GMT

As Per Uniqueid

latest 5 result

Re: Get Latest 5 Records

qlikview979 — Tue, 23 May 2017 10:04:55 GMT

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

Re: Get Latest 5 Records

Wed, 24 May 2017 04:17:58 GMT

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

Re: Get Latest 5 Records

Digvijay_Singh — Wed, 24 May 2017 04:45:33 GMT

Check this -

Re: Get Latest 5 Records

Kushal_Chawda — Wed, 24 May 2017 06:10:17 GMT

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;

Re: Get Latest 5 Records

jagan — Fri, 02 Jun 2017 08:40:59 GMT

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.