select top 2 observations in one field per distinct other field

[Anonymous]

Hi,

I have a problem that Im not sure can be solved by qlikview interface or the SQL used to load it. Essentially the basics of the table look like this:

id_ticket	id_comment	date_comment
1	1	01/05/2017
1	2	03/05/2017
2	3	01/05/2017
2	4	02/05/2017
2	5	03/05/2017

Its like a ticket system which has the unique id of each ticket: id_ticket, the unique id of each comment on that ticket: id_ticket and the date each comment was made.

Basically for each id_ticket, I need the time difference between the top 2 id_comment date values. As there are multiple comments, the min/max functions in SQL don't help.

Anyone have any ideas how this can be done?

[Anil_Babu_Samineni]

What is the output you are expecting from this Table

[Anonymous]

Something like this:

id_ticket | date_diff

1 | 2

2 | 1

where date_diff would be the difference between observations of date_comment from the top 2 id_comments. In this case for id_ticket = 1 there are only 2 id_comments and 2 date_comments, and so it is just their difference.

But for id_ticket = 2, there are 3 id_comment. In this case I would need the date difference of id_comment = 4 and id_comment =5.

I hope this makes sense

[Anil_Babu_Samineni]

May be this?

Qlikview Environment got changed to below

SET DateFormat='DD/MM/YYYY';

And Expression should be this in Straight table, Sorry If i miss understand the Rek

Date(Max(date_comment)) - Date(Min(date_comment))

[Anonymous]

Sorry if I wasn't clear previously, but the Max/Min functions aren't useful here as Im looking for the difference between top 2 observations from the date_comment field and not the min/max difference.

For id_ticket =2: Min/ Max would give the difference between dates related to id_comment = 5 and id_comment =3 when I need id_comment = 4 and id_comment = 3.

[tresesco · Accepted solution]

Take a straight table chart.

Dimension: id_ticket

Expression:

Num(

FirstSortedValue(date_comment, -id_comment) - FirstSortedValue(date_comment, -id_comment, 2)

, '#,##0' )

[Anonymous]

Thanks so much, FirstSortedValue is exactly what I was looking for!