Load dates greater than 27-12-2014 in the script editor

[#1]

I tried various ways to filter the dates greater than 27-12-2014 in de Script Editor of Qlikview.

I don't know where and if I use HEAVING or WHERE. Or maybe it can be done white another statement.

These are the setting:

SET ThousandSep='.';
SET DecimalSep=',';
SET MoneyThousandSep='.';
SET MoneyDecimalSep=',';
SET MoneyFormat='€ #.##0,00;€ -#.##0,00';
SET TimeFormat='h:mm:ss';
SET DateFormat='DD-MM-YYYY';
SET TimestampFormat='DD-MM-YYYY h:mm:ss[.fff]';
SET MonthNames='jan;feb;mrt;apr;mei;jun;jul;aug;sep;okt;nov;dec';
SET DayNames='ma;di;wo;do;vr;za;zo';

The table is loaded like below.

The StartDate column had a notation like: 2014-12-27 07:23:00,000

The  Year, Month, Week and Days are correct.

LOAD Name,
StartDate,
Year(StartDate),
Month(StartDate),
Week(StartDate),
Day(StartDate),
SQL SELECT Name,
StartDate,
FROM "Database".dbo.StartDates;

How do I load only the dates greater then 27-12-2014?

[buzzy996]

try this ,

LOAD Name,
StartDate,
Year(StartDate),
Month(StartDate),
Week(StartDate),
Day(StartDate),
SQL SELECT Name,
StartDate,
FROM "Database".dbo.StartDates

where urdatefield > '$(StartDate )';

[#3 · Accepted solution]

Hi Dennis,

It looks like your query is for a SQL Server database?  If so then you need to add a where clause after from (but before the semi colon) similar to the following:

HERE (StartDate >= CONVERT(DATETIME, '2015-01-31 00:00:00', 102))

If you have access to SQL Management Studio you can build the query using the Query > Design Query In Editor option which will allow you to enter the date in the filter column and will build the correct syntax for you.

If you need the start date to be flexible then, as Shiva suggested, use a QlikView variable to hold the start date and put that into your where clause.

Hope this helps.

Kind regards,

Rod

[#4]

use where condition as mentioned below.

WHERE CONVERT(VARCHAR(10),StartDate) > CONVERT(VARCHAR(10),'2014-12-27 07:23:00,000')

[jagan]

Hi Dennis,

Write the query in SQL with WHERE clause, and use the same in Qlikview.

Regards,

Jagan.

[#6]

Thanks Rod! This works (without the 102). What is the meaning of 102? Yes it a SQL database.

[jagan]

Hi,

Check this link for help regarding the parameter 102

w3schools.com/sql/func_convert.asp

Regards,

Jagan.

[#8]

Oke Thanks.

[#9]

Hi Dennis,

The 102 parameter is used by the Convert function to determine the output format.  102 represents the ANSI style and is reasonably common in SQL Server though there are a lot of other different versions you can have depending on your region.

The following page from Microsoft TechNet details the available styles: CAST and CONVERT

Kind regards,

Rod