topic Re: Value based on MAX Timestamp in QlikView

Value based on MAX Timestamp

chrisevans_cj — Wed, 25 Nov 2020 16:16:04 GMT

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 AM	660206241268677	1.0.0.6
31/8/2017 4:43:41 PM	660206241268677	1.0.0.5
31/8/2017 3:07:02 PM	660206241268677	1.0.0.5
31/8/2017 11:52:06 AM	867720587950660	1.0.0.5
31/8/2017 11:35:39 AM	867720587950660	1.0.0.5
31/8/2017 11:25:27 AM	867720587950660	1.0.0.5
31/8/2017 11:20:34 AM	867720587950660	1.0.0.5
31/8/2017 10:34:42 AM	867720587950660	1.0.0.5
31/8/2017 10:06:08 AM	660206241268677	1.0.0.5
31/8/2017 9:15:36 AM	660206241268677	1.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?

Re: Value based on MAX Timestamp

Anil_Babu_Samineni — Fri, 27 Oct 2017 11:17:14 GMT

Perhaps these as expressions?

1) Timestamp(Max(DATECREATED))

2) FirstSortedValue(RELEASENUMBER, -DATECREATED)

Re: Value based on MAX Timestamp

chrisevans_cj — Fri, 27 Oct 2017 11:39:39 GMT

I still have a two fields which are causing issues - any idea how to exclude Product and LineItemSales from FirstSortedValue(RELEASENUMBER, -DATECREATED)?

Re: Value based on MAX Timestamp

avinashelite — Fri, 27 Oct 2017 11:42:20 GMT

you could try with aggr() function

Aggr(max(DATECREATED),DEVICEID)

Re: Value based on MAX Timestamp

Anil_Babu_Samineni — Fri, 27 Oct 2017 11:44:03 GMT

Perhaps this?

FirstSortedValue({$<Product=, LineItemSales=>} RELEASENUMBER, -DATECREATED)

FirstSortedValue({<FieldName -= {'Product', 'LineItemSales'}>} RELEASENUMBER, -DATECREATED)

Re: Value based on MAX Timestamp

sunny_talwar — Fri, 27 Oct 2017 11:46:01 GMT

Exclude Product and LineItemSales? What exactly do you mean?

Re: Value based on MAX Timestamp

chrisevans_cj — Fri, 27 Oct 2017 11:51:12 GMT

Not returning what I'm looking for - this give the MAX date - which I've been able to get already

Re: Value based on MAX Timestamp

chrisevans_cj — Fri, 27 Oct 2017 11:51:55 GMT

Still getting the same result on the first one and second is not correct

Re: Value based on MAX Timestamp

Anil_Babu_Samineni — Fri, 27 Oct 2017 11:52:44 GMT

Then, I am with sunny.

Re: Value based on MAX Timestamp

avinashelite — Fri, 27 Oct 2017 11:53:39 GMT

What exactly your looking for ??

Re: Value based on MAX Timestamp

chrisevans_cj — Fri, 27 Oct 2017 11:55:36 GMT

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

Re: Value based on MAX Timestamp

sunny_talwar — Fri, 27 Oct 2017 11:57:49 GMT

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

Re: Value based on MAX Timestamp

chrisevans_cj — Fri, 27 Oct 2017 11:59:43 GMT

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

Re: Value based on MAX Timestamp

sunny_talwar — Fri, 27 Oct 2017 12:02:08 GMT

How about if you add DISTINCT

FirstSortedValue(DISTINCT RELEASENUMBER, -DATECREATED)

Re: Value based on MAX Timestamp

chrisevans_cj — Fri, 27 Oct 2017 12:08:19 GMT

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

Re: Value based on MAX Timestamp

chrisevans_cj — Fri, 27 Oct 2017 12:09:20 GMT

Thank you Sunny - hope you have a great weekend

Re: Value based on MAX Timestamp

effinty2112 — Fri, 27 Oct 2017 12:16:55 GMT

HI Chgris,

Maybe:

DEVICEID	Max(DATECREATED)	Concat(Aggr(if(Rank(DATECREATED,1,1)=1,RELEASENUMBER),DEVICEID,DATECREATED),',')
867720587950660	31/8/2017 11:52:06 am	1.0.0.5
660206241268677	2/9/2017 11:37:12 am	1.0.0.6