Fetch max date for two unique IDs

[rpavan17]

Hi,

Can you help me with below issue. Table as below. Need to get latest timestamp for each ID and Index

ID	Index	TimeStamp	Header 4
AAA	1001	05-01-2012  12:26:17 PM
AAA	1001	07-01-2012  3:59:07 PM
AAA	1002	06-01-2012  4:59:07 PM
BBB	1001	01-02-2012  4:26:22 PM
BBB	1002	02-02-2012  5:26:22 PM
BBB	1002	03-02-2012 5:26:22 PM

Result should be

ID	Index	TimeStamp	Header 4
AAA	1001	07-01-2012  3:59:07 PM
AAA	1002	06-01-2012  4:59:07 PM
BBB	1001	01-02-2012  4:26:22 PM
BBB	1002	03-02-2012 5:26:22 PM

Please help...

After that I have to find difference between timestamps of 1002 and 1001.

[francescoreggia]

Load

ID,

Index,

Max(TimeStamp) as TimeStamp

from...

Group by

ID,

Index;

[sunny_talwar]

Is this needed to be done in the script or front end?

[rpavan17]

Hi,

Anything would be fine. My goal is to get the time difference between index 1002 and 1001. Considering max date of each index

[sunny_talwar · Accepted solution]

How about this.... Index is not one of the dimension

Dimension

ID

Expression

Max({<Index = {'1001'}>}TimeStamp) - Max({<Index = {'1002'}>}TimeStamp)

If you need Index as one of the the dimensions... then may be this

Dimension

ID

Index

Expression

Max(TimeStamp) - Above(Max(TimeStamp))

[rpavan17]

I need ID only the dimension and need to get time difference between both index.

If i give, Max({<Index = {'1001'}>}TimeStamp) - Max({<Index = {'1002'}>}TimeStamp)

It is giving 1.32 as the value. How can i convert to hours if it comes more than 24 hours.

Example in excel: :mm:ss format

It is working if i select Duration. Can i use the same in script

[sunny_talwar]

May be this

Interval(Max({<Index = {'1001'}>}TimeStamp) - Max({<Index = {'1002'}>}TimeStamp), 'h:mm:ss')

[rpavan17]

Hi

Not working in Load Script.

Please help.

[HarishG]

Hi Pavan,

What Sunny has suggested works only in Front end, use that as Expression in a chart.

[sunny_talwar]

This is a front end function, won't be usable in the script... previously you mentioned that script or front end, either of them should be fine... but now you want this in script? Are you sure you want to pre-aggregate everything in the script?