topic Re: Load dates greater than 27-12-2014 in the script editor in QlikView

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

Tue, 07 Apr 2015 09:34:23 GMT

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?

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

buzzy996 — Tue, 07 Apr 2015 15:15:17 GMT

try this ,

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

where urdatefield > '$(StartDate )';

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

Wed, 08 Apr 2015 01:49:48 GMT

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

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

Wed, 08 Apr 2015 02:31:25 GMT

use where condition as mentioned below.

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

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

jagan — Wed, 08 Apr 2015 02:41:06 GMT

Hi Dennis,

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

Regards,

Jagan.

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

Wed, 08 Apr 2015 06:38:03 GMT

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

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

jagan — Wed, 08 Apr 2015 06:40:41 GMT

Hi,

Check this link for help regarding the parameter 102

w3schools.com/sql/func_convert.asp

Regards,

Jagan.

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

Wed, 08 Apr 2015 07:01:52 GMT

Oke Thanks.

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

Wed, 08 Apr 2015 08:58:47 GMT

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