topic Re: SQL don't select date range in QlikView

SQL don't select date range

Mon, 17 Nov 2014 10:24:56 GMT

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

Re: SQL don't select date range

ali_hijazi — Mon, 17 Nov 2014 10:34:14 GMT

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;

Re: SQL don't select date range

Mon, 17 Nov 2014 10:43:20 GMT

Thanks

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

Re: SQL don't select date range

ali_hijazi — Mon, 17 Nov 2014 10:47:43 GMT

then in the where condition put the following

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

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

Re: SQL don't select date range

Mon, 17 Nov 2014 10:49:54 GMT

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?

Re: SQL don't select date range

ali_hijazi — Mon, 17 Nov 2014 10:55:01 GMT

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

Re: SQL don't select date range

Mon, 17 Nov 2014 11:15:41 GMT

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.

Re: SQL don't select date range

ali_hijazi — Mon, 17 Nov 2014 11:19:43 GMT

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

Re: SQL don't select date range

Mon, 17 Nov 2014 11:21:16 GMT

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

Re: SQL don't select date range

ali_hijazi — Mon, 17 Nov 2014 11:27:13 GMT

weird !!

you are getting an OLEDB error

then it is not syntax error it is connection error

Re: SQL don't select date range

ali_hijazi — Mon, 17 Nov 2014 11:29:14 GMT

FYI

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,

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

Re: SQL don't select date range

Mon, 17 Nov 2014 11:30:25 GMT

This could be concatenate or case sensitive issue.

Normally Qlikview database connect syntaxes are very case sensitive. Check that execute statement case, if that's working fine.

Use aliases of columns from parent table in temp table.

Re: SQL don't select date range

Mon, 17 Nov 2014 11:55:16 GMT

same error

OLEDB read failed

SQL Execute [dbo].[DaySummary]

Re: SQL don't select date range

Mon, 17 Nov 2014 12:15:42 GMT

I get a different error now

Field not found - <_SessionStartedDate>

DaySummary:

noconcatenate

load * resident temp_table

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

This is the whole script

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

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

Re: SQL don't select date range

ali_hijazi — Mon, 17 Nov 2014 12:36:31 GMT

you should un qualify this field too

Re: SQL don't select date range

Mon, 17 Nov 2014 12:54:48 GMT

The script runs OK. So thanks for that

But all the other fields in the table have been renamed to include the temp table name.

For example DaySummary.Disconnects have been renamed to DaySummary.temp_table.Disconnects

How do i get rid of the temp_table bit?

Re: SQL don't select date range

ali_hijazi — Mon, 17 Nov 2014 12:57:53 GMT

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;