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

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

[Anonymous]

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.

[NZFei]

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