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 *;
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 *;
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.
same error
OLEDB read failed
SQL Execute [dbo].[DaySummary]
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 *;
you should un qualify this field too
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?
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;