topic Re: Row with missing timestamp is still selected in QlikView

Row with missing timestamp is still selected

Fri, 27 Mar 2015 14:50:16 GMT

Hello all,

I have a problem with logs that distribution service makes.

I want to get the name of tasks that have timestamp within some range, and I get the all of them, even it the value is null.

Here is the sample script:

TaskExecutionHistoryItemExtra:

load

date#(Left(TaskExecutionHistoryItem.StartTimeStamp,10)) as TaskExecutionHistoryItem.Date,

interval(Sum(TaskExecutionHistoryItem.Duration), 'hh:mm:ss') as TaskExecutionHistoryItem.TotalExecTime

Resident TaskExecutionHistoryItem_tmp

Where TaskExecutionHistoryItem.StartTimeStamp >= Date(Date#(Today(), 'DD.MM.YYYY')-8,'') & ' ' & '00:00:00'

and TaskExecutionHistoryItem.StartTimeStamp <= Date(Date#(Today(), 'DD.MM.YYYY')-1,'') & ' ' & '23:59:59'

and not Isnull(TaskExecutionHistoryItem.Duration) and Len(trim(TaskExecutionHistoryItem.Duration)) > 0

Group by

%TaskId,

TaskExecutionHistoryItem.ID,

TaskExecutionHistoryItem.StartTimeStamp,

TaskExecutionHistoryItem.EndTimeStamp,

TaskExecutionHistoryItem.Duration,

TaskExecutionHistoryItem.TaskStatus,

Month;

Any of these fields can contain null value, and I want tasks that have all info

TaskExecutionHistoryItem.StartTimeStamp,

TaskExecutionHistoryItem.EndTimeStamp,

TaskExecutionHistoryItem.Duration,

Re: Row with missing timestamp is still selected

anbu1984 — Sat, 28 Mar 2015 05:36:21 GMT

load

*,....

month

where Len(Trim(TaskExecutionHistoryItem.StartTimeStamp)) > 0 And Len(Trim(TaskExecutionHistoryItem.EndTimeStamp)) > 0 And Len(Trim(TaskExecutionHistoryItem.Duration)) > 0;

Re: Row with missing timestamp is still selected

Mon, 30 Mar 2015 14:40:47 GMT

It didnt work.

Re: Row with missing timestamp is still selected

anbu1984 — Mon, 30 Mar 2015 16:15:53 GMT

I just noticed at beginning of your post you mentioned, you need rows where field is null. Which field is this?

I want to get the name of tasks that have timestamp within some range, and I get the all of them, even it the value is null.

So you have two conditions?? One with above field is null OR below all fields should have value??

Any of these fields can contain null value, and I want tasks that have all info

TaskExecutionHistoryItem.StartTimeStamp,

TaskExecutionHistoryItem.EndTimeStamp,

TaskExecutionHistoryItem.Duration,

Can you post what you tried?

Re: Row with missing timestamp is still selected

Mon, 30 Mar 2015 16:24:20 GMT

Hi there,

I meant that, even if I specify the range, I still get the rows where the Timestamp is null.

Re: Row with missing timestamp is still selected

anbu1984 — Tue, 31 Mar 2015 06:04:09 GMT

Can you post your script?