How to compute # of concurrent users from any IoT, applications, systems in a customizable way?
For computing the number of concurrent users for any needs, this approach might be of some help to the Qlik community by taking advantage of its desired data algorithm (scripting) and structure capabilities. We will use Do While and Autogenerate to iterate and create a tall structure with timestamps by minute for each user and then summarize by minute, hour, and day to show examples of application of such approach. While there may be many other approaches available, the purpose of this post is to show (demystify 🙂 how one can take advantage of programming capabilities within Qlik to create data structures for computing answers for seemingly complex problems. Also one would benefit by paying attention to nuances of functions and semantics when working with date, datetime, and also variables used in the examples provided in this post.
Mock-Data example:
RECORD_ID, Login ,Logout , User_ID
2799860 ,1/5/2015 16:17 ,1/14/2015 16:25 ,66UH
2799892 ,1/5/2015 16:22 ,1/5/2015 16:22 ,ZEJS
2799924 ,1/5/2015 16:29 ,1/5/2015 17:31 ,DJF2
2800229 ,1/5/2015 17:39 ,1/5/2015 17:40 ,HE6E
2800301 ,1/5/2015 18:05 ,1/5/2015 18:13 ,HQ2R
2800309 ,1/5/2015 18:08 ,1/5/2015 19:55 ,3SK3
//note the above mock data example was obtained by doing a google search to quickly put together a proof of concept.
Variable declaration:
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY hh:mm';
Step 1: Extract facts data with starttime, endtime, userid. Compute #minutes so that you can simply work with 1 datetime field and not 2 different ones. AutoID used so we can loop through each log iteratively in next step.
Facts:
Load
//RECORD_ID,
AutoNumber(RECORD_ID) as ID,
Login,
Logout,
User_ID,
if(Login=Logout, 1,ceil((Logout-Login)*1440)) as LogMinutes
FROM [lib://ConcurrentUserData/SampleData.txt]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq);
Step 2: Compute counts of logs
Temp:
Load
count(ID) as countLogs
Resident Facts;
Step 3: For each log we will generate a record for each minute of logged time by using an outer loop to first go one log at a time and then through an inner loop for each minute at a time:
Set x=0;
Do While x <= Num(Peek('countLogs', 0, 'Temp'))+1;
Let vMinutes = num(Peek('LogMinutes',$(x),'Facts'));
Let vUserID = Peek('User_ID',$(x),'Facts');
Let vLogin = num(Peek('Login',$(x),'Facts'));
Tall:
Load
'$(vUserID)' as User_ID,
TimeStamp($(vLogin)+((IterNo()-1)/1440)) as LogTimeStamp,
num(TimeStamp($(vLogin)+((IterNo()-1)/1440))) as numLogTimeStamp
AutoGenerate 1 While IterNo() <= '$(vMinutes)';
Let x = x + 1;
Loop;
LogTimeStamps:
Load
*,
text(LogTimeStamp) as textLogTimeStamp
Resident Tall;
Drop Table Tall;
Step 4: Example of count of concurrent users by minute, avg. number by hour, and avg. number by day concurrency in script:
MinuteConcurrent:
Load
count(User_ID) as MinuteConcurrent,
textLogTimeStamp,
hour(timestamp(timestamp#(textLogTimeStamp, 'm/d/yyyy hh:mm'))) as Hour,
day(Floor(timestamp#(textLogTimeStamp, 'm/d/yyyy hh:mm'))) as Day
Resident LogTimeStamps
Group by textLogTimeStamp;
HourConcurrent:
Load
avg(MinuteConcurrent) as HourConcurrent,
Hour
Resident MinuteConcurrent
Group by Hour;
DayConcurrent:
Load
avg(MinuteConcurrent) as DayConcurrent,
Day
Resident MinuteConcurrent
Group by Day;
Step 5: Simply create tables on front-end with Hour, HourConcurrent; Minute, MinuteConcurrent etc. to see results.
Sandeep Sarawgi
