Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
ekaponkratova
Contributor

Find a date before an N event

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

 

Labels (1)
1 Solution

Accepted Solutions
Partner
Partner

Re: Find a date before an N event

 

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:

TableTemp.PNG

 

 

 

 

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:

TableFinal.PNG

View solution in original post

12 Replies
Partner
Partner

Re: Find a date before an N event

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

 

ekaponkratova
Contributor

Re: Find a date before an N event

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 )

 

Partner
Partner

Re: Find a date before an N event

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;

 

???

 

 

ekaponkratova
Contributor

Re: Find a date before an N event

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

q_1.png

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

q_2.png

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.

 

Partner
Partner

Re: Find a date before an N event

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?

ekaponkratova
Contributor

Re: Find a date before an N event

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

q_6.png

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.

q_7.png

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.

q_8.png

Partner
Partner

Re: Find a date before an N event

 

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:

TableTemp.PNG

 

 

 

 

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:

TableFinal.PNG

View solution in original post

ekaponkratova
Contributor

Re: Find a date before an N event

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

ekaponkratova
Contributor

Re: Find a date before an N event

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.