Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Fill missing data

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?

21 Replies
Not applicable
Author

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.

maxgro
MVP
MVP

in script you can adapt this (generate all dates between start and end date)

Generating Missing Data In QlikView

pag. 10

hic
Former Employee
Former Employee

MarcoWedel

Hi Alex,

one possible solution:

QlikCommunity_Thread_119296_Pic4.JPG.jpg

QlikCommunity_Thread_119296_Pic3.JPG.jpg

tabConnectionStats:

LOAD *,

     AutoNumberHash128(SessionStartedDate, SessionClosedDate) as %SessionIntervalID;

LOAD SessionId,

     Username,

     DisconnectionReason,

     SessionStartedDate,

     If(IsNum(SessionClosedDate), SessionClosedDate, Today()) as SessionClosedDate

FROM [http://community.qlik.com/servlet/JiveServlet/download/532979-107411/sample%20data%20connection%20st...]

(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

Not applicable
Author

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 ;

hic
Former Employee
Former Employee

You have "...Firm , SQL SELECT...". It should be "...Firm ; SQL SELECT...".

HIC

jagan
Luminary Alumni
Luminary Alumni

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.

Not applicable
Author

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 ;

jagan
Luminary Alumni
Luminary Alumni

Hi,

Check whether the dates are coming in SessionClosedDate and SessionClosedTime fields.

Regards,

jagan.