Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
Not applicable

SQL don't select date range

How would I adapt the following script to exclude rows where the SessionStartedDate was in the range 2014-11-03 and 2014-11-09?

QUALIFY *;

UNQUALIFY SessionStartedDate;

DaySummary:

LOAD

    Date(DayStart(Date(Date,'DD/MM/YYYY')),'YYYY-MM-DD') As SessionStartedDate,

  Users,

  Disconnects,

  Reconnects,

  WorkingDayDisconnects,

  WorkingDayReconnects;

SQL Execute [dbo].[DaySummary];

UNQUALIFY *;

Tags (2)
1 Solution

Accepted Solutions
Partner
Partner

Re: SQL don't select date range

QUALIFY *;

UNQUALIFY SessionStartedDate;

temp_table:

Load

  Date(DayStart(Date(Date,'DD/MM/YYYY')),'YYYY-MM-DD') As SessionStartedDate,

  num(DayStart(Date)) as _SessionStartedDate,

  Users,

  Disconnects,

  Reconnects,

  WorkingDayDisconnects,

  WorkingDayReconnects;

SQL Execute [dbo].[DaySummary];

unqualify *;

DaySummary:

noconcatenate

load * resident temp_table

where _SessionStartedDate <num(daystart(makeDate(2014,11,3))) or  _SessionStartedDate > num(dayStart(makedate(2014,11,9)));

drop table temp_table;

I can walk on water when it freezes
16 Replies
Partner
Partner

Re: SQL don't select date range

temp_table:

Load

your_fields

num(DayStart(Date)) as SessionStartedDate

;

sql execute [DBO].[DaySummary];

table:

noconcatenate

load * resident temp_table

where SessionStartedDate >num(daystart(makeDate(2014,11,3))) and SessionStartedDate < num(dayStart(makedate(2014,11,9)));

drop table temp_table;

I can walk on water when it freezes
Not applicable

Re: SQL don't select date range

Thanks

have you got the < and > signs the wrong way round though? I need to EXCLUDE data from that week, not include it.

Partner
Partner

Re: SQL don't select date range

then in the where condition put the following

where SessionStartedDate <=num(daystart(makeDate(2014,11,3))) or  SessionStartedDate

>= num(dayStart(makedate(2014,11,9)));

I can walk on water when it freezes
Not applicable

Re: SQL don't select date range

I also need to to keep the date in the same format, as it it links to another table.

Is there a way of doing it without altering the the format of the SessionStartedDate field?

Partner
Partner

Re: SQL don't select date range

keep your SessionStartedDate as is

and my column above name it anything you want and apply the condition on my column

but I prefer to have the dates in the QlikView format (numbers) and I format them on the chart for end users

I can walk on water when it freezes
Not applicable

Re: SQL don't select date range

I get this error

OLEDB read failed

SQL Execute [dbo].[DaySummary]

Which is strange as I don't get it when I run the script i posted in my original question.

Partner
Partner

Re: SQL don't select date range

can you send me a screen shot of your script to detect any error?

I can walk on water when it freezes
Not applicable

Re: SQL don't select date range

QUALIFY *;

UNQUALIFY SessionStartedDate;

temp_table:

Load

  Date(DayStart(Date(Date,'DD/MM/YYYY')),'YYYY-MM-DD') As SessionStartedDate,

  Users,

  Disconnects,

  Reconnects,

  WorkingDayDisconnects,

  WorkingDayReconnects,

num(DayStart(Date)) as SessionStartedDate;

SQL Execute [dbo].[DaySummary];

DaySummary:

noconcatenate

load * resident temp_table

where SessionStartedDate <num(daystart(makeDate(2014,11,3))) or  SessionStartedDate> num(dayStart(makedate(2014,11,9)));

drop table temp_table;

UNQUALIFY *;

Partner
Partner

Re: SQL don't select date range

weird !!

you are getting an OLEDB error

then it is not syntax error it is connection error

I can walk on water when it freezes