topic Testing for duplicate timelines in QlikView

Testing for duplicate timelines

Tue, 20 Dec 2016 10:49:34 GMT

Good Day Qlikview geniuses...

How would one test for duplicate timelines if you have a (Originally a unix timestamp)Date Field DD-MM-YYYY a Time from Field hh:mm and a Time to field also hh:mm.

Now basicly I need to check that on the same DATE field there isn't more that one row where the two time fields for that day could intersect.

Practical example: An IT support technician inputs his/her date of dealing with a call and the time from anf to spent on that call. Sometimes by accident or human error their date's timelines intersect each other hence between call 1 08:00 hours to 12:00 hours and call 2 from 11:40 to 13:00 hours on the same date there is an error because the timelines intersect i.e. by 20 minutes on the same day.

If they do intersect it means the IT Tech's time is being double booked. Henc I wish to display both intersecting timelines.

Regards,

Heinrich

Re: Testing for duplicate timelines

adamdavi3s — Tue, 20 Dec 2016 13:16:05 GMT

Hi Heinrich,

I would test this in the load script using previous on in the front end using 'before' or 'above'

If you have a search on here then there should be many threads which should help you out.

Re: Testing for duplicate timelines

adamdavi3s — Tue, 20 Dec 2016 13:27:17 GMT

Here is a quick example of previous (wanted to refresh my memory) there might be a neater way of doing this but this flags both rows


//load our data into a temp table
tmp:


load date(start, 'DD/MM/YYYY hh:mm:ss') as start, date(end, 'DD/MM/YYYY hh:mm:ss') as end;
LOAD * INLINE [


start,end
42724.5457086806,42724.5873753472
42724.6701157407,42724.7035821017
42724.7127686295,42724.7223902525
42724.640089132,42724.6770561259
42724.7333916693,42724.7651089855
42724.7751203203,42724.8136669279
42724.5998508548,42724.6383613843




];


NoConcatenate //switch off any concatenation to force new table


//load this data into a new table, noting the order by so we can use previous
tmp2:
LOAD rowno() as line,start,end, if(Previous(end) >= start, 1,0) as overlapflag
resident tmp order by start asc;


drop table tmp;


fact:
//finally flip this into our fact table so we can flag the second of the overlap rows
load *,if(previous(overlapflag) =1,1,0) as overlapflag2
resident tmp2
order by line desc;


drop table tmp2;