timestamps

[#1]

Hi there,

would appreciate your advice.

I have to calculate how long before flight departure time airport confirmes parking stand number for plane.

Every flight has unique id number which contains lots of timestamps and other data.

On most of occasions airport plans stands for arriving plane in avdance and then before plane lands, stand gets confirmed:

0 stands for unconfirmed stand number,

1 stands for confirmed stand number.

STD	LAST_UPDATED	CONFIRMED_STAND	ONSTAND
7:25:00	3:13:52	0	10
7:25:00	6:23:42	0	4
7:25:00	6:24:23	0	3
7:25:00	6:38:42	0	4
7:25:00	6:38:55	1	4
7:25:00	6:50:22	1	4
7:25:00	6:58:31	1	4
7:25:00	7:04:54	1	4
7:25:00	7:06:23	1	4
7:25:00	7:17:50	1	4
7:25:00	7:21:23	1	4
7:25:00	7:26:04	1	4
7:25:00	7:28:23	1	4
7:25:00	7:58:11	1	4

So in this example  everything is straight forward:

scheduled departure time is 7:25:00,

stand number gets changed few times before it is confirmed,

stand 4 gets confirmed at 6:38:55,

I calculate time when stand gets confirmed as follows:

STD - min({$<CONFIRMED_STAND = {"1"}>} LAST_UPDATED)

Problem appears when on some occasions stand number gets confirmed then unconfirmed, then confirmed again:

STD	LAST_UPDATED	CONFIRMED_STAND	ONSTAND
7:50:00	7:50:20	0	7
7:50:00	8:10:35	0	3
7:50:00	8:11:29	1	3
7:50:00	8:11:44	0	10
7:50:00	8:11:58	1	10
7:50:00	8:15:08	0	8
7:50:00	8:15:20	1	8
7:50:00	8:28:55	1	8

We can see that stand has been confirmed few times in given example, the truth is plane goes to stand which is confirmed as last in table, stand 8 gets confirmed at 08:15:20 which is 25min after departure time meaning that plane is arriving late.

My STD - min({$<CONFIRMED_STAND = {"1"}>} LAST_UPDATED) does not work here as I don't want min value.

The value I need is first timestamp which is greater than max timestamp when confirmed stand = 0.

How do I calculate this in qlikview?

Thank you,

Lauris

[swuehl · Accepted solution]

Attached a sample QVW with two approaches

a) expression only:

=Time(

Min( Aggr( if( LAST_UPDATED > Max(TOTAL<STD> {<CONFIRMED_STAND = {0}>} LAST_UPDATED), LAST_UPDATED), STD, LAST_UPDATED))

)

(here I've added STD as aggr() dimension to be able to distinguish between different flights, use flight number if available instead)

b) script based:

You can flag the latest confirmed timestamp like

LOAD *,

if(CONFIRMED_STAND = 0, Previous(LAST_UPDATED)) as Latest_Confirmed

RESIDENT INPUT

ORDER BY LAST_UPDATED desc;

Then find the max confirmed timestamp using max. (with a bit more effort, you can do also this in the script.

[#3]

Absolutely beautiful. Thanks a lot for this.

I wish I was able to open your attachment, but as I have personal edition so I can’t.

I’ve used expression which works fine. As advised I replaced STDs with ID number which is unique number for each flight.

To get clear picture what expression does I wanted to ask, what does this bit of expression mean:

TOTAL<STD>

In my case I replaced it with ID number, does it describe search area of LAST_UPDATED within one flight?

Once again thanks a lot,

Lauris

[swuehl]

It tells QV to calculate MAX per STD across all LAST_UPDATED.

Create a straight table chart with dimension STD and LAST_UPDATED and expression

Max(TOTAL<STD> {<CONFIRMED_STAND = {0}>} LAST_UPDATED), LAST_UPDATED)

to see what I mean.