Find a date before an N event

[Anonymous]

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

[NZFei]

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

[Anonymous]

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 )

[NZFei]

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;

???

[Anonymous]

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.

[NZFei]

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?

[Anonymous]

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.

[NZFei · Accepted solution]

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:

[Anonymous]

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

[Anonymous]

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.