Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
rpavan17
Creator
Creator

Fetch max date for two unique IDs

Hi,

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

IDIndexTimeStampHeader 4
AAA100105-01-2012  12:26:17 PM
AAA100107-01-2012  3:59:07 PM
AAA100206-01-2012  4:59:07 PM
BBB100101-02-2012  4:26:22 PM
BBB100202-02-2012  5:26:22 PM
BBB100203-02-2012 5:26:22 PM

Result should be

IDIndexTimeStampHeader 4
AAA100107-01-2012  3:59:07 PM
AAA100206-01-2012  4:59:07 PM
BBB100101-02-2012  4:26:22 PM
BBB100203-02-2012 5:26:22 PM

Please help...

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

1 Solution

Accepted Solutions
sunny_talwar

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))

View solution in original post

9 Replies
francescoreggia
Partner
Partner

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
Creator
Creator
Author

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

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
Creator
Creator
Author

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
Creator
Creator
Author

Hi

Not working in Load Script.

Capture.JPG

Please help.

HarishG
Partner
Partner

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?