topic Re: Fetch max date for two unique IDs in QlikView

Fetch max date for two unique IDs

rpavan17 — Mon, 16 Jul 2018 10:58:34 GMT

Hi,

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

ID	Index	TimeStamp
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
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.

Re: Fetch max date for two unique IDs

francescoreggia — Mon, 16 Jul 2018 11:11:54 GMT

Load

ID,

Index,

Max(TimeStamp) as TimeStamp

from...

Group by

ID,

Index;

Re: Fetch max date for two unique IDs

sunny_talwar — Mon, 16 Jul 2018 11:13:52 GMT

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

Re: Fetch max date for two unique IDs

rpavan17 — Mon, 16 Jul 2018 11:22:27 GMT

Hi,

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

Re: Fetch max date for two unique IDs

sunny_talwar — Mon, 16 Jul 2018 11:25:24 GMT

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

Dimension

Expression

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

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

Dimension

Index

Expression

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

Re: Fetch max date for two unique IDs

rpavan17 — Mon, 16 Jul 2018 11:37:58 GMT

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

Re: Fetch max date for two unique IDs

sunny_talwar — Mon, 16 Jul 2018 11:40:40 GMT

May be this

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

Re: Fetch max date for two unique IDs

rpavan17 — Tue, 17 Jul 2018 05:30:32 GMT

Not working in Load Script.

Please help.

Re: Fetch max date for two unique IDs

HarishG — Tue, 17 Jul 2018 06:54:28 GMT

Hi Pavan,

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

Re: Fetch max date for two unique IDs

sunny_talwar — Tue, 17 Jul 2018 11:12:29 GMT

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?