topic Re: How do i compare two records with time component? in QlikView

How do i compare two records with time component?

amien — Wed, 25 Nov 2020 16:16:04 GMT

I have these 4 records:

date, time from, time to, name, machine

1/1/2018, 11:00, 17:00, Mister X, Machina A

1/1/2018, 15:00, 19:00, Mister X, Machina B

1/1/2018, 11:00, 15:00, Mister Y, Machina C

1/1/2018, 16:00, 19:00, Mister Y, Machina D

the thing is that Mister X is planned double. He needs to work on Machine A and B at the same time (which is not possible). There is an overlap in time between 15:00 and 17:00

What i want to add is another field : conflict.

date, time from, time to, name, machine, conflict

1/1/2018, 11:00, 17:00, Mister X, Machina A, 1

1/1/2018, 15:00, 19:00, Mister X, Machina B, 1

1/1/2018, 11:00, 15:00, Mister Y, Machina C, 0

1/1/2018, 16:00, 19:00, Mister Y, Machina D, 0

Mister X has on both records a 1 because there is a conflict on time. Mister Y also has to work on 2 machines on the same day, but timewise there is no issue.

How can i solve this?

Thanks in advanced

Re: How do i compare two records with time component?

sunny_talwar — Fri, 29 Dec 2017 18:09:22 GMT

May be this

Table:

LOAD *,

TimeStamp(date + [time from]) as Datetime_from,

TimeStamp(date + [time to]) as Datetime_to

INLINE [

date, time from, time to, name, machine

1/1/2018, 11:00, 17:00, Mister X, Machina A

1/1/2018, 15:00, 19:00, Mister X, Machina B

1/1/2018, 11:00, 15:00, Mister Y, Machina C

1/1/2018, 16:00, 19:00, Mister Y, Machina D

];

Left Join (Table)

LOAD name,

Max(Flag) as conflict

Group By name;

LOAD *,

If(name = Previous(name), If(Datetime_from <= Previous(Datetime_to), 1, 0), 0) as Flag

Resident Table

Order By name, Datetime_from;

Re: How do i compare two records with time component?

amien — Fri, 29 Dec 2017 19:36:49 GMT

Thank! Works great!

I added : date = Previous(date) and added date as field in the left join.