Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 *;
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;
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;
Thanks
have you got the < and > signs the wrong way round though? I need to EXCLUDE data from that week, not include it.
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 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?
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 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.
can you send me a screen shot of your script to detect any error?
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 *;
weird !!
you are getting an OLEDB error
then it is not syntax error it is connection error