Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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 😉
Hi, any solution?. Thanks!
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 😉
Hi David, the solution works fine !!! Thank you very much for the help!