Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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
ali_hijazi
Honored Contributor

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;

16 Replies
ali_hijazi
Honored Contributor

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;

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.

ali_hijazi
Honored Contributor

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)));

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?

ali_hijazi
Honored Contributor

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

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.

ali_hijazi
Honored Contributor

Re: SQL don't select date range

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

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 *;

ali_hijazi
Honored Contributor

Re: SQL don't select date range

weird !!

you are getting an OLEDB error

then it is not syntax error it is connection error

Community Browser