Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am analysing some connection stats and need to know how many unique users are logged on each day. Some sample data is attached.
The problem I have is that a user's session may start on a Monday and end on a Friday, therefore I need to include all days from when their session started, through to when it ended.
How on earth do i go about doing that?
Can I do it in chart? Or do I need to do something in the dataload beforehand?
Hi Alex, Create the Calendar table for Max and Min date in the transactions table and remove the date if it is Sunday or Sat.
in script you can adapt this (generate all dates between start and end date)
Generating Missing Data In QlikView
pag. 10
Exactly this problem is described here:
http://community.qlik.com/blogs/qlikviewdesignblog/2013/02/12/reference-dates
HIC
Hi Alex,
one possible solution:
tabConnectionStats:
LOAD *,
AutoNumberHash128(SessionStartedDate, SessionClosedDate) as %SessionIntervalID;
LOAD SessionId,
Username,
DisconnectionReason,
SessionStartedDate,
If(IsNum(SessionClosedDate), SessionClosedDate, Today()) as SessionClosedDate
(ooxml, embedded labels, table is Sheet1);
tabCalendar:
LOAD
Date,
WeekName(Date) as Week,
MonthName(Date) as Month,
QuarterName(Date) as Quarter,
Year(Date) as Year;
LOAD
Date(MinDate+IterNo()-1) as Date
While MinDate+IterNo()-1<=MaxDate;
LOAD
Min(SessionStartedDate) as MinDate,
Max(SessionClosedDate) as MaxDate
Resident tabConnectionStats;
tabDateLink:
IntervalMatch(Date)
LOAD Distinct
SessionStartedDate,
SessionClosedDate
Resident tabConnectionStats;
Left Join (tabDateLink)
LOAD
SessionStartedDate,
SessionClosedDate,
AutoNumberHash128(SessionStartedDate, SessionClosedDate) as %SessionIntervalID
Resident tabDateLink;
DROP Fields SessionStartedDate, SessionClosedDate From tabDateLink;
hope this helps
regards
Marco
Hi Henric,
this is great. Thanks for your help AGAIN! I get the following error message though
Error in expression:
')' expected
UserSessionDetails_x_Dates:
Load SessionId,
Date( SessionStartedDate + IterNo() – 1 ) as ReferenceDate
Resident UserSessionDetails
While IterNo() <= SessionClosedDate - SessionStartedDate + 1
This is the script I am using. What could I be doing wrong?
UserSessionDetails:
LOAD UserName,
SessionId,
Date(DayStart(Date(SessionStarted,'DD/MM/YYYY')),'YYYY-MM-DD') As SessionStartedDate,
SessionStarted,
Date(DayStart(Date(SessionClosed,'DD/MM/YYYY')),'YYYY-MM-DD') As SessionClosedDate,
SessionClosed,
Firm,
SQL SELECT *
FROM dbo.usersessiondetails;
UserSessionDetails_x_Dates:
Load UserName,
Date( SessionStartedDate + IterNo() – 1 ) as ReferenceDate
Resident UserSessionDetails
While IterNo() <= SessionClosedDate - SessionStartedDate + 1 ;
You have "...Firm , SQL SELECT...". It should be "...Firm ; SQL SELECT...".
HIC
HI,
Check this
UserSessionDetails:
LOAD UserName,
SessionId,
Date(DayStart(Date(SessionStarted,'DD/MM/YYYY')),'YYYY-MM-DD') As SessionStartedDate,
SessionStarted,
Date(DayStart(Date(SessionClosed,'DD/MM/YYYY')),'YYYY-MM-DD') As SessionClosedDate,
SessionClosed,
Firm;
SQL SELECT *
FROM dbo.usersessiondetails;
UserSessionDetails_x_Dates:
Load UserName,
Date( SessionStartedDate + IterNo() – 1 ) as ReferenceDate
Resident UserSessionDetails
While IterNo() <= SessionClosedDate - SessionStartedDate + 1 ;
You need to give ; after firm in above script.
Regards,
Jagan.
Hi Jagan,
actually the ; is in my script. I just removed lost of fields from the example above, and forgot to add in the ;
My full script is below which still gives the error
UserSessionDetails:
LOAD UserName,
SessionId,
Environment,
Replace(DisconnectionReason, 'ClientConnectionClosed. Reason:','') As DisconnectionReason,
Date(DayStart(Date(SessionStarted,'DD/MM/YYYY')),'YYYY-MM-DD') As SessionStartedDate,
MakeTime(Hour(SessionStarted), Minute(SessionStarted), Second(SessionStarted)) As SessionStartedTime,
Hour(SessionStarted) As SessionStartedHour,
SessionStarted,
Date(DayStart(Date(SessionClosed,'DD/MM/YYYY')),'YYYY-MM-DD') As SessionClosedDate,
MakeTime(Hour(SessionClosed), Minute(SessionClosed), Second(SessionClosed)) As SessionClosedTime,
Hour(SessionClosed) As SessionClosedHour,
SessionClosed,
FlashPlayer,
Firm,
IPAddress,
TestAccount,
Disconnection,
Reconnection,
GeneralOutage,
FirmOutage,
WorkingDayDisconnect,
WorkingDayReconnect;
SQL SELECT *
FROM dbo.usersessiondetails;
UserSessionDetails_x_Dates:
Load UserName,
Date( SessionStartedDate + IterNo() – 1 ) as ReferenceDate
Resident UserSessionDetails
While IterNo() <= SessionClosedDate - SessionStartedDate + 1 ;
Hi,
Check whether the dates are coming in SessionClosedDate and SessionClosedTime fields.
Regards,
jagan.