topic Fill missing data in QlikView

Fill missing data

Wed, 21 May 2014 13:29:13 GMT

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?

Re: Fill missing data

Wed, 21 May 2014 17:31:06 GMT

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.

Re: Fill missing data

maxgro — Wed, 21 May 2014 19:34:20 GMT

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

Generating Missing Data In QlikView

pag. 10

Re: Fill missing data

hic — Wed, 21 May 2014 21:09:37 GMT

Exactly this problem is described here:

http://community.qlik.com/blogs/qlikviewdesignblog/2013/02/12/reference-dates

HIC

Re: Fill missing data

MarcoWedel — Wed, 21 May 2014 21:41:56 GMT

Hi Alex,

one possible solution:

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%20stats.xlsx]
(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

Re: Fill missing data

Fri, 23 May 2014 09:10:07 GMT

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 ;

Re: Fill missing data

hic — Fri, 23 May 2014 09:24:34 GMT

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

HIC

Re: Fill missing data

jagan — Fri, 23 May 2014 09:25:32 GMT

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.

Re: Fill missing data

Fri, 23 May 2014 09:29:26 GMT

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 ;

Re: Fill missing data

jagan — Fri, 23 May 2014 10:16:05 GMT

Hi,

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

Regards,

jagan.

Re: Fill missing data

Fri, 23 May 2014 10:35:32 GMT

Sorry, what eaxctly do you mean Jagan?

Re: Fill missing data

jagan — Fri, 23 May 2014 10:38:29 GMT

Hi,

You create a list boxes for SessionClosedDate and SessionClosedTime fields and check whether you got any values into it. Can you attach some sample file with some data?

Regards,

Jagan.

Re: Re: Fill missing data

Fri, 23 May 2014 10:47:52 GMT

Yes, there is data

Sample data attached

Re: Re: Fill missing data

Fri, 23 May 2014 10:53:25 GMT

Hi henric,

As I mentioned to Jagan, that was actually a typo. My script does contain the ;

The full script is:

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 ;

Re: Fill missing data

Fri, 23 May 2014 12:32:49 GMT

I worked out the problem, the first dash wasn't recognised as a minus sign for some reason. Having copied it from Henric's blog it pasted as a dash rather than a minus sign

Re: Fill missing data

Fri, 23 May 2014 12:51:56 GMT

I worked out the problem, the first dash wasn't recognised as a minus sign for some reason. Having copied it from your blog it pasted as a dash rather than a minus sign

Re: Fill missing data

Fri, 23 May 2014 13:25:58 GMT

I have just realised a slight problem I have is that the SessionClosed date is sometimes blank.

If I wanted to assume a blank value was today. How would I do that?

Re: Fill missing data

Fri, 23 May 2014 13:26:10 GMT

I have just realised a slight problem I have is that the SessionClosed date is sometimes blank.

If I wanted to assume a blank value was today. How would I do that?

Re: Fill missing data

MarcoWedel — Fri, 23 May 2014 13:44:40 GMT

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

just like used in my solution ...

regards

Marco

Re: Fill missing data

Fri, 23 May 2014 14:53:29 GMT

Thanks Marco

but how do I combine it with my existing script?

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;

Sorry for all the questions. This is all a little new to me!