topic Re: Assume blank value as today in QlikView

Assume blank value as today

Fri, 23 May 2014 13:30:42 GMT

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?

Re: Assume blank value as today

maxgro — Fri, 23 May 2014 13:33:45 GMT

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

Re: Assume blank value as today

Fri, 23 May 2014 13:40:31 GMT

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 ;

Re: Assume blank value as today

maxgro — Fri, 23 May 2014 13:46:41 GMT

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;

Re: Assume blank value as today

Fri, 23 May 2014 14:47:23 GMT

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;

Re: Assume blank value as today

PrashantSangle — Fri, 23 May 2014 14:55:12 GMT

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,

Re: Assume blank value as today

PrashantSangle — Fri, 23 May 2014 14:56:56 GMT

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,

Re: Assume blank value as today

maxgro — Fri, 23 May 2014 14:59:33 GMT

could you post your qlik doc?