Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
chrisevans_cj
Contributor III
Contributor III
‎2017-10-27 07:07 AM

Value based on MAX Timestamp

Hi,

I'm trying to get the values of a field for the corresponding MAX Timestamp. This will be in straight chart. Below sample data:

DATECREATED DEVICEID RELEASENUMBER
2/9/2017 11:37:12 AM6602062412686771.0.0.6
31/8/2017 4:43:41 PM6602062412686771.0.0.5
31/8/2017 3:07:02 PM6602062412686771.0.0.5
31/8/2017 11:52:06 AM8677205879506601.0.0.5
31/8/2017 11:35:39 AM8677205879506601.0.0.5
31/8/2017 11:25:27 AM8677205879506601.0.0.5
31/8/2017 11:20:34 AM8677205879506601.0.0.5
31/8/2017 10:34:42 AM8677205879506601.0.0.5
31/8/2017 10:06:08 AM6602062412686771.0.0.5
31/8/2017 9:15:36 AM6602062412686771.0.0.5

The Straight Chart should be:

DEVICEID                    MAX(DATECREATED)          RELEASENUMBER

660206241268677           2/9/2017 11:37:12 AM                 1.0.0.6

867720587950660           31/8/2017 11:52:06 AM               1.0.0.5


Any advise?

3,806 Views
0 Likes
16 Replies
chrisevans_cj
Contributor III
Contributor III
‎2017-10-27 07:55 AM
Author

In response to sunny_talwar

in the full dataset - there are lines which are repeated due to various products sold on a transaction. The items also have different prices - so to get the correct answer i need to exclude those in the expression so that the expression can return a single answer

1,061 Views
0 Likes
sunny_talwar
MVP
MVP
‎2017-10-27 07:57 AM

In response to chrisevans_cj

Is this part of a field? What is that field name called?

1,061 Views
0 Likes
chrisevans_cj
Contributor III
Contributor III
‎2017-10-27 07:59 AM
Author

In response to avinashelite

Capture.PNG

due to the multiple lines (Product and Product Line Price) some of the devices don't calculate the Releasenumber

1,061 Views
0 Likes
sunny_talwar
MVP
MVP
‎2017-10-27 08:02 AM

In response to chrisevans_cj

How about if you add DISTINCT

FirstSortedValue(DISTINCT RELEASENUMBER, -DATECREATED)

3,689 Views
0 Likes
chrisevans_cj
Contributor III
Contributor III
‎2017-10-27 08:08 AM
Author

In response to sunny_talwar

2.PNG

Above to the left we have the straight chart. Please note the dash on the 2 line.

When looking at the data for the transaction which happened you will see that there are 6 products sold, which are either 10 or 20 in value. If I select and product - then the straight table shows the correct release version. The dash is due to multiple lines - so i need to ignore them to get the releasenumber

1,061 Views
0 Likes
chrisevans_cj
Contributor III
Contributor III
‎2017-10-27 08:09 AM
Author

In response to sunny_talwar

Thank you Sunny - hope you have a great weekend

1,061 Views
effinty2112
Master
Master
‎2017-10-27 08:16 AM

HI Chgris,

Maybe:

DEVICEID Max(DATECREATED) Concat(Aggr(if(Rank(DATECREATED,1,1)=1,RELEASENUMBER),DEVICEID,DATECREATED),',')
86772058795066031/8/2017 11:52:06 am1.0.0.5
6602062412686772/9/2017 11:37:12 am1.0.0.6
1,061 Views
0 Likes