topic Re: getting the latest record based on time stamp in QlikView

getting the latest record based on time stamp

Tue, 06 Oct 2015 16:14:32 GMT

Hi all

I have a requirement of fetching the Customer_ID with the latest time stamp, what ever being the ITEM he had purchased.

Header 1	Header 2	Header 3
ID	ITEM	TIME_STAMP
123	BOOKS	8/18/2015 13:11
123	PEN	8/18/2015 13:58
123	PENCIL	8/19/2015 15:55
999	PEN	7/17/2013 16:44
777	BOOKS	7/27/2015 19:25
777	PENCIL	7/29/2015 12:01
777	BOOKS	7/29/2015 12:01

I HAVE TO DISPLAY THE RECORD BASED ON LATEST TIMESTAMP IN A STRAIGHT TABLE.

Re: getting the latest record based on time stamp

Clever_Anjos — Tue, 06 Oct 2015 16:24:17 GMT

FirstSortedValue(Customer_ID,-yourTimeStamp)

Re: getting the latest record based on time stamp

malini_qlikview — Tue, 06 Oct 2015 16:26:07 GMT

you can get the max(Timestamp) grouped on Customer_ID and join it to the original table in the script

or in front end you can use a calculated dimension as below

=Timestamp(Aggr(Max(Timestamp#(TIME_STAMP,'MM/DD/YYYY hh:mm')),ID))

Re: getting the latest record based on time stamp

Peter_Cammaert — Tue, 06 Oct 2015 16:26:55 GMT

Create a straight table with the required fields, add and hide a dummy expression (e.g. something like =1, or =RowNo()), order by timestamp (Descending) and in Dimension Limits select "Show only" + "First" + value 1.

Best

Peter

Re: getting the latest record based on time stamp

Digvijay_Singh — Tue, 06 Oct 2015 16:53:31 GMT

Dimension - ID,Item

Exp - as mentioned below -

Firstsortedvalue will miss the 777 as 2 values are same,not sure as of now how it can be handled in firstsortedvalue. For equal timestamp, do you want both values or anyone?

Re: getting the latest record based on time stamp

Anonymous — Tue, 06 Oct 2015 20:47:19 GMT

Use the FirstSortedvalue() Function as Suggested by Anjos , It is working perfectly for your requirement. Thanks Anjo for telling this function which I really don't know before.