Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
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 *;

1 Solution

Accepted Solutions
Highlighted
Partner
Partner

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

View solution in original post

16 Replies
Highlighted
Partner
Partner

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
Highlighted
Not applicable

Thanks

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

Highlighted
Partner
Partner

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
Highlighted
Not applicable

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?

Highlighted
Partner
Partner

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
Highlighted
Not applicable

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.

Highlighted
Partner
Partner

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

I can walk on water when it freezes
Highlighted
Not applicable

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

Highlighted
Partner
Partner

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