Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

NewUser1122
New Contributor

Need Help with IntervalMatch

I am importing two spreadsheets into QlikView:

LOAD [Agent Name],
Date,
[Activity Start Time],
[Activity End Time],
[Activity Type],
[Activity Duration],
[Activity Detail]

OutboundCalls:
LOAD DATE,
TIMESTAMP,
[AGENT NAME] as [Agent Name],
[AGENT GROUP],
SKILL,
CALLS,
[Call Count],
TIME,
[Last Name,First Name Formula]

Ultimately, I want to produce a table which links the Time from tabOutboundCalls to the corresponding Activity Start Time and Activity End Time in tabScheduledInboundTime, so that I can see how many CALLS (also from tabOutboundCalls) occurred during that interval, by Agent Name.  Something like this:

DateAgent NameActivity Start TimeActivity End TimeTIMECALLS
2/28/2019Smith, John10:30:0012:15:0010:46:021
2/28/2019Smith, John10:30:0012:15:0011:19:181

 

I believe I should be able to use IntervalMatch to get the result I am looking for, but cannot get the syntax right. Any help is greatly appreciated! 

1 Reply
MindaugasBacius
Valued Contributor III

Re: Need Help with IntervalMatch

Try something like this:

OutboundCalls:
LOAD DATE,
TIMESTAMP,
Date + TIMESTAMP as DateTime
[AGENT NAME] as [Agent Name],
[AGENT GROUP],
SKILL,
CALLS,
[Call Count],
TIME,


tmp:
LOAD [Agent Name],
Date,
[Activity Start Time],
[Activity End Time],
Date + [Activity Start Time] as start
Date + [Activity End Time] as end [Activity Type], [Activity Duration], [Activity Detail] Left Join (tmp) IntervalMatch(DateTime, [Agent Name]) LOAD start ,end ,[Agent Name] Resident tmp; Left Join (OutboundCalls) LOAD * Resident tmp; DROP Table tmp;

Understand the logic behind the script:

1. You have to connect both tables with IntervalMatch. Also to add new field DateTime to tmp table.

2. Then Left join the tmp table that have two Keys [Agent Name] and DateTime. The same fields are in OutboundCalls.

 

If it not connects at first make the same format of the DateTime, start and end fields.

 

IntervalMatch examples:

https://community.qlik.com/t5/New-to-QlikView/IntervalMatch-multiple-key/td-p/641915

https://community.qlik.com/t5/QlikView-App-Development/Interval-Match-or-Inline-load/td-p/559006