Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Partner
Partner

Re: SQL don't select date range

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

I can walk on water when it freezes
Not applicable

Re: SQL don't select date range

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.

Not applicable

Re: SQL don't select date range

same error

OLEDB read failed

SQL Execute [dbo].[DaySummary]

Not applicable

Re: SQL don't select date range

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

Partner
Partner

Re: SQL don't select date range

you should un qualify this field too

I can walk on water when it freezes
Not applicable

Re: SQL don't select date range

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?

Highlighted
Partner
Partner

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;

I can walk on water when it freezes