Need help to find top 2 rows based on latest entry

[sayadutt]

Hi Team,

I have the below table.

Requirement is to display top 2 rows which has the largest number from the latest date.

Date	Name	Time
7/1/18	A	10.24
7/1/18	B	12
7/1/18	C	23
7/2/18	A	4
7/2/18	B	4
7/2/18	C	2.34
7/3/18	A	34
7/3/18	B	24
7/3/18	C	34.34

In this case, 7/3/18 is the latest one and top 2 rows displayed should be: 

Date	Name	Time
7/3/18	C	34.34
7/3/18	A	34

Date field is a variable, and each day new records will be added. We need to print the top 2 rows from latest date.

Can you please help.

Thanks

[qlikviewwizard]

Hi,

Try like this.

[sayadutt]

Hi Arjun,

My requirement is to show the top 2 values from LATEST date.

In your case it shows the top 2 but not based on LATEST date.

Can you please tell me how I can only show the last date's value

[qlikviewwizard]

As per the data latest Date is 7/3/18. Its two values are 34.4 and 34. Your requirement also the same.

You wrote:

Can you please tell me how I can only show the last date's value

What is the meaning of this. Can you explain in detail?

[prasanth_nossam]

Hi Sayantan Dutt

try this

=Aggr(if(Rank(Total((Time)))<=2,Time),Time)

[sayadutt]

Hi Arjun,

If I add a new entry in the table and run your expression it still shows the overall top 2.

What I need is from the last run, which all are top 2

LOAD DATE#(Date,'MM/DD/YY') as Date,Name,Time INLINE [

Date,Name,Time

7/1/18,A,10.24

7/1/18,B,12

7/1/18,C,23

7/2/18,A,4

7/2/18,B,4

7/2/18,C,2.34

7/3/18,A,34

7/3/18,B,24

7/3/18,C,34.34

7/4/18,A,24.10

7/4/18,B,14.10

7/4/18,C,10.10

];

Your exp gives

7/3/18,A,34

7/3/18,C,34.34

What I need is: (From last run which is 7/4/18)

7/4/18,A,24.10

7/4/18,B,14.10

Thanks

[sayadutt]

Hi Prashant,

I need top 2 from last execution (based on date) and not overall.

Can you please suggest what exp to use? It should ONLY consider the last run date.

Thanks

[sunny_talwar]

Try this

=Sum({<Time = {"=Rank(sum({<Date = {[$(=Date(Max(Date)))]}>}Time))<=2"}, Date = {"$(=Date(Max(Date)))"}>}Time)

[qlikviewwizard]

Hi

Please try this. This is working fine. I thoroughly tested.

Data1:

LOAD DATE(DATE#(Date,'MM/DD/YY'),'DD-MMM-YYYY') as Date,Name, Time INLINE [

Date,Name,Time

7/1/18,A,10.24

7/1/18,B,12

7/1/18,C,23

7/2/18,A,4

7/2/18,B,4

7/2/18,C,2.34

7/3/18,A,34

7/3/18,B,24

7/3/18,C,34.34

7/4/18,A,24.10

7/4/18,B,14.10

7/4/18,C,10.10

];

Concatenate

Data2:

LOAD *,1 as Flag,

AutoNumber(Time) as Rank

Resident Data1 where AutoNumber(Time)<=2

Order By Date desc;

[prasanth_nossam]

Hi Dutta

Logic should apply to the refreshed data (on new dates)  right.