Assume blank value as today

[#1]

I have a field called SessionDate, which is sometimes blank. When it's blank i want to assume today's date.

How would i do this in my data load?

[maxgro]

if(len(trim(SessionDate))=0, num(today()), SessionDate) as SessionDate

[#3]

Sorry, slight change to the field name. It is the SessionClosedDate field which is sometimes blank

How would I incorporate that in my load script

My load script is as follows

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 SessionId,

          Date( SessionStartedDate + IterNo() - 1 ) as SessionDate

          Resident UserSessionDetails

          While IterNo() <= SessionClosedDate - SessionStartedDate + 1 ;

[maxgro]

maybe you can replace your last statement with

Tmp:

Load SessionId,

          Date( SessionStartedDate + IterNo() - 1 ) as SessionDate

          Resident UserSessionDetails

          While IterNo() <= SessionClosedDate - SessionStartedDate + 1 ;

UserSessionDetails_x_Dates:

NoConcatenate

  load

  SessionId,

  if(len(trim(SessionDate))=0, num(today()), SessionDate) as SessionDate

Resident

  Tmp;

drop Table Tmp;          

[#5]

Hi Massimo,

it was my fault in the first post. It is actually the SessionClosedDate field which is sometimes blank, however when i substitute this in your script is doesn't work

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;

Tmp:

Load SessionId,

          Date( SessionStartedDate + IterNo() - 1 ) as SessionDate

          Resident UserSessionDetails

          While IterNo() <= SessionClosedDate - SessionStartedDate + 1 ;

UserSessionDetails_x_Dates:

NoConcatenate

  load

  SessionId,

  if(len(trim(SessionClosedDate))=0, num(today()), SessionClosedDate) as SessionClosedDate

Resident

  Tmp;

drop Table Tmp;

[PrashantSangle]

Hi

Replace this with your SessionStartedDate Field.

if(isnull(SessionStarted),

Date(DayStart(Date(Today(),'DD/MM/YYYY')),'YYYY-MM-DD'),

Date(DayStart(Date(SessionStarted,'DD/MM/YYYY')),'YYYY-MM-DD')

) As SessionStartedDate

Regards,

PS

[PrashantSangle]

Hi,

Try this

if(isnull(SessionClosed),

Date(DayStart(Date(Today(),'DD/MM/YYYY')),'YYYY-MM-DD'),

Date(DayStart(Date(SessionClosed,'DD/MM/YYYY')),'YYYY-MM-DD')

) As SessionClosedDate

Regards,

PS

[maxgro]

could you post your qlik doc?