Computing # concurrent number of users by minute, hour, day from any IoT, applications, or systems.
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.
//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.
