topic Re: Getting the latest value based on timestamp in QlikView

Getting the latest value based on timestamp

Mon, 28 Dec 2015 17:19:34 GMT

Hi All

I have three fields in a straight table

Employee_ID Swipe_Time Meeting_No

ABC123 28/09/2015 10:38:37 meeeting2

ABC123 28/09/2015 10:44:17 meeeting3

ABC123 28/09/2015 09:12:07 meeeting1

QWE756 12/03/2015 09:12:07 meeeting1

QWE756 18/03/2015 06:10:00 meeeting2

IJK143 14/09/2015 04:00:07 meeeting1

IJK143 14/09/2015 09:30:07 meeeting2

I want the latest meeting attended by an employee based on timestamp(swipe_time),

Only the record with the maximum time stamp should be considered

My output should be

Employee_ID Swipe_Time Meeting_No

ABC123 28/09/2015 10:44:17 meeeting3

QWE756 18/03/2015 06:10:00 meeeting2

IJK143 14/09/2015 09:30:07 meeeting2

Re: Getting the latest value based on timestamp

swuehl — Mon, 28 Dec 2015 17:30:51 GMT

Use employee id as dimension, then two expressions

=TimeStamp( Max(Swipe_Time) )

=FirstSortedvalue(Meeting_No ,-Swipe_Time)

edit:

Added Timestamp() formatting if needed

Re: Getting the latest value based on timestamp

sunny_talwar — Mon, 28 Dec 2015 17:31:18 GMT

Try this:

Dimension: Employee_ID

Expression1: =TimeStamp(Max(Swipe_Time))

Expression2: =FirstSortedValue(Meeting_No, -Swipe_Time)

UPDATE: Sample attached:

Re: Getting the latest value based on timestamp

Sun, 03 Jan 2016 10:00:16 GMT

Hi thanks for the reply, but i'm not getting the result same as you. I'm getting null values in place of the second expression could you please help me

Re: Getting the latest value based on timestamp

Sun, 03 Jan 2016 10:09:22 GMT

Thanks for the reply
I'm not getting the expected out and i'm getting null values in place of expression 2(this is the main thing i have to display) please help me, can we do any thing in the back end script to get the correct result

Re: Getting the latest value based on timestamp

Sun, 03 Jan 2016 10:09:39 GMT

Re: Getting the latest value based on timestamp

sunny_talwar — Sun, 03 Jan 2016 14:00:20 GMT

Would you be able to share your qvw or a sample qvw where you are seeing nulls?

Re: Getting the latest value based on timestamp

Peter_Cammaert — Sun, 03 Jan 2016 15:20:55 GMT

Make sure that QlikView recognizes Swipe_time as a DateTime (Timestamp) field, and not just as a string. FirstSortedValue() will only return NULL values if the second parameter proves to be non-numeric.

We can't help you any further with the limited details from your original post and the message that "it doesn't work".