topic Re: Max (and Min) Timestamp in script per dimension in QlikView

Max (and Min) Timestamp in script per dimension

Sat, 02 Jan 2016 11:29:08 GMT

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

Re: Max (and Min) Timestamp in script per dimension

Anonymous — Sat, 02 Jan 2016 16:53:44 GMT

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

;

Re: Max (and Min) Timestamp in script per dimension

Oleg_Troyansky — Sat, 02 Jan 2016 16:58:55 GMT

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!

Re: Max (and Min) Timestamp in script per dimension

maxgro — Sat, 02 Jan 2016 18:19:25 GMT

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;

Re: Max (and Min) Timestamp in script per dimension

Sun, 03 Jan 2016 07:59:48 GMT

@ 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.

Re: Max (and Min) Timestamp in script per dimension

aj0031724 — Fri, 24 Mar 2017 12:06:05 GMT

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?