Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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