Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

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

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?

1 Solution

Accepted Solutions
Not applicable

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

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

8 Replies
buzzy996
Honored Contributor II

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

try this ,

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

where urdatefield > '$(StartDate )';

Not applicable

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

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

Not applicable

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

use where condition as mentioned below.

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

MVP & Luminary
MVP & Luminary

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

Hi Dennis,

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

Regards,

Jagan.

Not applicable

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

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

MVP & Luminary
MVP & Luminary

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

Hi,

Check this link for help regarding the parameter 102

w3schools.com/sql/func_convert.asp

Regards,

Jagan.

Not applicable

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

Oke Thanks.

Not applicable

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

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

Community Browser