QlikSense-Telephone Call Concurrency based on Start and End Time
I am new to Qlik and have been posed with a challenge that I cannot get my head around.
I have daily call data records that contain call start and end times. The Customer would like to know at any point in time in a day what the maximum number of Calls occurring at the same time is to check for sizing requirements.
Start Time - End Time
10:00:23 - 10:01:28 Call 1
10:01:10 - 10:20:51 Call 2
10:01:12 - 10:23:23 Call 3
10:24:01 - 10:24:21 Call 4
Max Call Concurrency = 3
The files are 24 hour files and based on PBX Customers.
Figure out how your customer defines "any point in time". Is that once an hour, every minute, every second?
Generate a helper table that contains those moments.
Do an interval match between that helper table and your CDRs. Warning: This will create a freakingly huge table if you have a lot of long calls and your customer insists on "every second".
Now do a count the number of occurrences of every moment.
If your customer wants to be able to slice-and-dice this data, you have to keep this huge table in your data model. But if not, you can save yourself a lot of memory by making a new table with only the moment and the amount that values shows up and drop the huge table.
I hope the above makes sense.
Don't allow Perfection to be the enemy of Good Enough. May you live in interesting times!