Relate tasks and delivery notes by date

[josembaeza]

Hello to everyone! It's the first time I write in this forum, I've learned a lot here!

I wrote to ask you how I can solve a problem I have in Qlik and I do not know how to solve it. I have two tables ::

TABLE 1: Here we record what kind of task we are doing, the start time and the end time. The structure would be more like this:

Load * inline

[

User, dt_inicio_tarea, dt_fin, task,

Pepe, 27/09/2018 7:00:10, 27/09/2018 7:30:10, Clear

Alejandro, 27/09/2018 7:00:00, 27/09/2018 7:00:10, Pack

Pepe, 27/09/2018 7:00:10, 27/09/2018 7:00:10, Packing

Matthew, 9/28/2018 7:00:10, 9/27/2018 3:00:00 PM, Seal

];

TABLE 2: Here we record all the work by way of delivery notes. All tasks are recorded by way of delivery notes, similar to the following:

Load * inline

[

User, dt_albaran, id_albaran,

Pepe, 09/27/2018 7:31:10, 10001

Pepe, 9/27/2018 7:32:10, 10002

Matthew, 09/27/2018 7:31:10, 10003

Juan, 09/27/2018 7:33:10, 10004

Pepe, 09/27/2018 7:34:10, 10005

Alejandro, 09/27/2018 7:31:10, 10006

];

BOTH TABLES ARE RELATED BY THE USER.

Taking into account these two tables, I have a table that shows all the delivery notes made, the date, and the user. What I need to know is which task corresponds to each delivery note, taking into account that the delivery note has a date and time and that these are within a range that is recorded in the table of tasks.

I have tried like this but it does not work,

if (date (dt_albaran) = date (dt_inicio_tarea) and time (dt_albaran) <time (dt_fin_tarea) and time (dt_albaran)> time (dt_inicio_tarea), task)

Any ideas?. Thank you very much.

[josembaeza]

Hi, any solution?. Thanks!

[DavidŠtorek · Accepted solution]

Hi,

for this case would be appropriate solution to use interval match. It helps you to fit time from Table2 to time interval in Table 1 (see example 2 in lik above )

In your case it should look like

Inner Join
IntervalMatch ( dt_albaran, User)
LOAD dt_inicio_tarea, dt_fin, User
Resident TABLE 1;

Hope that helps you 😉

[josembaeza]

Hi David, the solution works fine !!! Thank you very much for the help!