topic Re: Find a date before an N event in App Development

Find a date before an N event

Anonymous — Sat, 16 Nov 2024 21:54:15 GMT

Good day All,

It's good to have Qlik back. I have not realized until this downtime that all the discussion is happening here.

Situation: I have a col in one table - Estimated departure. I have a move col in another table. The dates in the move col could be < or >= than the Estimated departure date. I need to find the next datetime to Estimated departure.

For example, the departure time is 11/13/2018 05:00 AM. The move col contains: 11/14/2018 04:00 AM; 11/14 02:00AM; 11/13/2018 06:AM; 11/13/2018 04:45 AM; 11/13/2018 03:00 AM. I want it to return 11/13/2018 04:45 AM.

The below piece worked just fine with IFs until yesterday when I got cases of dates being 35 values away from the Departure date.... So, there should be another way.

I found different posts on how to find the closest date, but nothing seems to be suitable for my case.

Many thanks,

Eka

Re: Find a date before an N event

NZFei — Wed, 14 Nov 2018 01:54:44 GMT

As you know, behind the scene, any time stamp is a number.

For example the time stamp "9/11/2018 12:34:46" is the number 43413.524143519.

Maybe you can use your move times, minus Estimated departure and then find out the minimum difference.

For example the departure time (with the date format D/MM/YYYY) in number format is: num('13/11/2018 05:00:00')=43417.208333333.

The difference with 11/13/2018 04:45 AM is num('13/11/2018 04:45:00')=43417.197916667-43417.208333333=-0.010416666.

The difference with 11/13/2018 03:00 AM is num('13/11/2018 03:00:00')=43417.125-43417.208333333=-0.083333333.

The expression is something like this:

min(fabs(num(MoveColTimeStamp)-num(DepartureTime)))

Hope you understand what I am talking about...

Fei

Re: Find a date before an N event

Anonymous — Wed, 14 Nov 2018 02:01:08 GMT

Hi,

Thank you for the response! How will it work? I imagine in the non-Qlik world: loop through the entire move col and find the date where

min(fabs(num(MoveColTimeStamp)-num(DepartureTime)))

is minimum. I don't know how to do it practically )

Re: Find a date before an N event

NZFei — Wed, 14 Nov 2018 02:10:51 GMT

When you mentioned "two tables", are they the tables in QlikView script? How do they look like?

For example:

Table1:

load

SomeID,

DepartureTime

From somewhere;

Table2:

load

SomeID,

MoveTime

From Somewhere2;

???

Re: Find a date before an N event

Anonymous — Wed, 14 Nov 2018 02:26:05 GMT

Table 1 with the vessel information where 1 vessel has 1 date:

Table 2 with the move information where 1 crane working for 1 vessel can make several moves.

As of now I am grouping by the Vessel & Crane (see my piece) and use Ifs to find the closest date.

So, what I need to do is to find the MAX time for every crane (!) working on a particular vessel that is < Est Departure date.

Re: Find a date before an N event

NZFei — Wed, 14 Nov 2018 02:27:31 GMT

OK so for table 1 the key fields are vesslID and departure time.

For table 2, the key fields are vesslID, Crane and Move time. One vesslID may have many lines.

I don't understand what you mean by " the MAX time for every crane (!) working on a particular vessel that is < Est Departure date". Can you please explain using some examples?

Re: Find a date before an N event

Anonymous — Wed, 14 Nov 2018 02:37:51 GMT

Sure. In the example below we got five vessels. Let's take the one in yellow as an example.

From the move table, you see that only one crane worked for our chosen vessel - but there could be up to 8 cranes working on 1 vessel.

What I need is to get something like a table below which shows Vessel name, crane and min and Max time.

Max time should be < than the Departure time. And obviously, the departure time will be different for different vessels but the same for all cranes working on that vessel.

Re: Find a date before an N event

NZFei — Wed, 14 Nov 2018 03:08:51 GMT

Temp:

Load

VESSEL_VISIT_C,

NUM(EST_DPTR_D) AS EST_DPTR_D

FROM Table 1;

left join (Temp)

load

VESSEL_VISIT_C,

CRANE,

NUM(TET) AS TET

FROM Table 2;

By here you should have the data like this:

Continue the script:

Final:
load
VESSEL_VISIT_C,
CRANE,
Timestamp(min(TET)) as Min,
Timestamp(max(TET)) as Max
Resident Temp
where TET<EST_DPTR_D
Group by
VESSEL_VISIT_C,
CRANE;

DROP Table Temp;

You will have the result you want:

Re: Find a date before an N event

Anonymous — Wed, 14 Nov 2018 03:11:45 GMT

The result looks what is expected! Thank you! Let me try!

Re: Find a date before an N event

Anonymous — Wed, 14 Nov 2018 04:22:01 GMT

My feedback s far - it's good but it seems to be slow i.e. I am loading just a one-month sample with 150,000 rows. It has been already 10 minutes on the move table.

Re: Find a date before an N event

Anonymous — Wed, 14 Nov 2018 04:26:48 GMT

My feedback s far - it's good but it seems to be slow i.e. I am loading just a one-month sample with 150,000 rows. It has been already 10 minutes on the move table.

Re: Find a date before an N event

Anonymous — Wed, 14 Nov 2018 04:41:20 GMT

May be what I should do is to put the restriction when loading the data from the db i.e. TET should be no more than Departure + 1 day. And then, use your formula.

I guess the steps are to:

1. Load move

2. Load vessel

3. Create a new move table where TET < departure + 1 day

4. Use your formula

But on the second thought, it will be the same load.

Re: Find a date before an N event

NZFei — Wed, 14 Nov 2018 19:31:13 GMT

If what you mean is this part:

left join (Temp)

load

VESSEL_VISIT_C,

CRANE,

NUM(TET) AS TET

FROM Table 2;

Then I can't help with it as it is just a very straight load. Setting some "Where" clause when loading from the database should help...

Fei