Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

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

Re: Fill missing data

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
Not applicable

Re: Fill missing data

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

Generating Missing Data In QlikView

pag. 10

Henric_Cronström
Not applicable

Re: Fill missing data

MarcoWedel
Not applicable

Re: Fill missing data

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

Re: Fill missing data

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 ;

Henric_Cronström
Not applicable

Re: Fill missing data

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

HIC

jagan
Not applicable

Re: Fill missing data

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

Re: Fill missing data

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
Not applicable

Re: Fill missing data

Hi,

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

Regards,

jagan.