Fill missing data

[#1]

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?

[#2]

Sorry, what eaxctly do you mean Jagan?

[jagan]

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.

[#4]

Yes, there is data

Sample data attached

[#5]

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 ;

[#6]

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

[#7]

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

[#8]

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?

[#9]

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?

[MarcoWedel]

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

just like used in my solution ...

regards

Marco

[#11]

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!