Max (and Min) Timestamp in script per dimension

[#1]

Hi all,

I am new to Qlikview developing and I am turning to you for some help .

Data:

Operational Train data with departure and arrival stations, departure and arrival times. Key Field: TRAINRUN_ID.

There can be multiple lines in a table like underneath. (using a simple table object)

TRAINRUN_ID	ATD	ATA
12149872	01-06-2015 22:51	02-06-2015 00:08
12149872	02-06-2015 02:40
12149872	02-06-2015 13:23	02-06-2015 18:19
12149872	02-06-2015 18:49	02-06-2015 22:28
12149872	03-06-2015 00:58	03-06-2015 06:30
12149872		02-06-2015 10:52

What I need is the maximum arrival timestamp of the trainrun.

This would look something like this:

TRAINRUN_ID	ATD	ATA	Max_ATA
12149872	01-06-2015 22:51	02-06-2015 00:08	03-06-2015 06:30
12149872	02-06-2015 02:40		03-06-2015 06:30
12149872	02-06-2015 13:23	02-06-2015 18:19	03-06-2015 06:30
12149872	02-06-2015 18:49	02-06-2015 22:28	03-06-2015 06:30
12149872	03-06-2015 00:58	03-06-2015 06:30	03-06-2015 06:30
12149872		02-06-2015 10:52	03-06-2015 06:30

Optimizing the data load (these extra fields are of course unnecessary) I can do as a second step, meaning to keep just one line with the start and the end time :

TRAINRUN_ID	ATD	Max_ATA
12149872	01-06-2015 22:51	03-06-2015 06:30

The script I was trying:

EBIS_TRSB:

LOAD TRAINRUN_ID,

     timestamp(TIME_START_REAL,'DD-MM-YYYY hh:mm') as ATD,

     timestamp(TIME_END_REAL,'DD-MM-YYYY hh:mm') as ATA

     From

     DATASET..

Max_Timestamp_Arrival_Table:

Load    TRAINRUN_ID,

            Max(ATA) as Max_Timestamp_Arrival

            Resident EBIS_TRSB;

Here I get an error message because of the Max function not working here. I tried to find the problem with this and tried some solutions given in discussions here, but couldn't make it work, would be great if someone could help!

(Maybe it would be best to use a minimum and maximum timestamp, I don't know).

[Anonymous · Accepted solution]

You need to add a Group By clause to you final load, see bold red below.

[I have fiddled with your timestamps as well so they display properly]

DATASET:

LOAD TRAINRUN_ID,

    ATD,

    ATA

FROM

[https://community.qlik.com/thread/198151]

(html, codepage is 1252, embedded labels, table is @1);

EBIS_TRSB:

NoConcatenate

LOAD TRAINRUN_ID,

    timestamp(timestamp#(ATD,'DD-MM-YYYY hh:mm')) as ATD,

    timestamp(timestamp#(ATA,'DD-MM-YYYY hh:mm')) as ATA

    resident      DATASET

    ;

drop table DATASET;

Max_Timestamp_Arrival_Table:

Load    TRAINRUN_ID,

            timestamp(Max(ATA)) as Max_Timestamp_Arrival

            Resident EBIS_TRSB

            Group By TRAINRUN_ID

            ;

[Oleg_Troyansky]

You are almost there. In order to calculate MAX per Trainrun ID, you need to add a GROUP BY clause, like this:

Max_Timestamp_Arrival_Table:

Load   

          TRAINRUN_ID,

          Min(ATD) as Min_Timestamp_Departure,

          Max(ATA) as Max_Timestamp_Arrival

Resident

          EBIS_TRSB

GROUP BY

          TRAINRUN_ID

;

You can learn more about QlikView scripting syntax and practice advance techniques using my new book QlikView Your Business.

cheers,

Oleg Troyansky

Take your Qlik Skills to the next level at the Masters Summit for Qlik - now with new and redesigned materials!

[maxgro]

DATASET:

load * inline [

TRAINRUN_ID, ATD, ATA

12149872, 01-06-2015 22:51, 02-06-2015 00:08

12149872, 02-06-2015 02:40,

12149872, 02-06-2015 13:23, 02-06-2015 18:19

12149872, 02-06-2015 18:49, 02-06-2015 22:28

12149872, 03-06-2015 00:58, 03-06-2015 06:30

12149872, , 02-06-2015 10:52

];

Max_Timestamp_Arrival_Table:

LOAD TRAINRUN_ID,

     timestamp(min(timestamp#(ATD,'DD-MM-YYYY hh:mm'))) as MinATD,

     timestamp(max(timestamp#(ATA,'DD-MM-YYYY hh:mm'))) as MaxATA

Resident DATASET

Group By TRAINRUN_ID;

DROP Table DATASET;

[#5]

@ Bill: This is what I asked for thanks it works, I kept the timestamps as I had them though, still worked well .

@ Oleg: Very good add of the minimum, I can use this as well .

@ Maxgro: similar input to Oleg his script, also works thanks.

[aj0031724]

Dear Bill,

I need help regards to same logic of hour calculation.

Need your help to figure out the possible solution.

There are tow time columns (Uptime and Downtime of any event/alarm) and we need to achieve like :

An event will pass a time filter if for any time duration, the event  was active within the time defined by the time filter.

For example->

Table structure like:

DATE	ALARMUPTIME	ALARMDOWNIME	ALARMID
3/3/2017	3/3/2017 17:15	1
3/4/2017	3/3/2017 17:15	1
3/5/2017	3/3/2017 17:15	3/5/2017 16:47	1

Here Alarmid=1 has uptime of  3/3/2017 17:15 AND finished on downtime of =3/5/2017 16:47.

So it remain active till 3/5/2017 16:47.

So if below are filter selections then:

Exmaple 1 ) Date filter: 3/3/2017, 5/3/2017

Hour Filter: 16:00

Alarm Up time: 3/3/2017 17:15:06

Alarm Down time: 5/3/2017 15:47:13

Calculation->Fails the time filter (Events not vsisble)

Exmaple 2 ) Date filter: 3/3/2017, 3/4/2017, 5/3/2017

Hour Filter: 16:00

Alarm Up time: 3/3/2017 17:15:06

Alarm Down time: 5/3/2017 15:47:13

Calculation ->Pass the time filter.(Events will be visible)

Can you please advise how can I achive something like above to pass/fail  the hour filter?