Need Help with IntervalMatch

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

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:

Date	Agent Name	Activity Start Time	Activity End Time	TIME	CALLS
2/28/2019	Smith, John	10:30:00	12:15:00	10:46:02	1
2/28/2019	Smith, John	10:30:00	12:15:00	11:19:18	1

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!

Re: Need Help with IntervalMatch

MindaugasBacius — Fri, 01 Mar 2019 05:47:39 GMT

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

topic Need Help with IntervalMatch in QlikView

Need Help with IntervalMatch

Re: Need Help with IntervalMatch