topic Re: Importing a table using a date filter in QlikView

Importing a table using a date filter

Mon, 22 Apr 2013 12:00:20 GMT

Hello all,

I am trying to load data from an MS Access MDB file into Qlikview (The connection to the Access Database works fine). The problem is that I want to load only a subset of this data, based on today's date.

In more detail: the data has a field that contains 'planned hours' (called Hours_Planning), and Ionly want to load the future planned hours, starting from 'today'. below is an excerpt of the code:

SQL SELECT

     [Week] as [Week_Planning],
     [ProjectID] as [ProjectID_Planning],
     [PlanningID] as [PlanningID_Planning],
     [PersonnelID] as [EmployeeNumber_Planning],
     [Hours] as [Hours_Planning]

FROM tblPlanning;

The field [Week_Planning] should only be imported with dates greater than (or equal to) today.

I already tried the following 3 where-statements, but to no avail.

where DATE([Week_Planning], 'DD-MM-YYYY')>=DATE(01-01-2012, 'DD-MM-YYYY'); //we tried with a hardcoded startdate of January 1, as well as Today()
where [Week_Planning] >='$Today()';
where([Week_Planning])>=(Today());

Thank you in advance!

Michelle

Re: Importing a table using a date filter

Mon, 22 Apr 2013 17:29:30 GMT

Can you try:

WHERE [Week_Planning] >= Date(Today()) ;

Matt

Re: Importing a table using a date filter

Mon, 22 Apr 2013 17:30:32 GMT

Sorry, just realized that likely will not work, can you send an example of the [Week] field?

Re: Importing a table using a date filter

Mon, 22 Apr 2013 17:32:28 GMT

If the week field returns a number, 1 - 52, try this:

WHERE [Week_Planning] >= week(today()) ;

Re: Importing a table using a date filter

Mon, 22 Apr 2013 17:43:12 GMT

if you say date(week_planinng) you will only get a date eg. 22 and you cannot filter on 22

so where week_planning >= '2011-01-01'

for instance if you want a hardcoded day.

Do you want the todays date

week_planning >= Today()

HOWEVER - make sure the date Today is givning you is the same format as in Week_planning

Normally its SET DateFormat='DD-MM-YYYY'; for my country settings

Check that yours are the same as in week_planning

Re: Importing a table using a date filter

Tue, 23 Apr 2013 12:15:15 GMT

Hello all,

Thanks so much for the quick replies!

@Mattsies,

The field [Week] (imported as [Week_Planning]) just contains a date in the format DD-MM-YYYY. (e.g. 03-09-2012).

From all your replies, I have tried adding:

- WHERE [Week_Planning] >= Date(Today());
- where ([Week_Planning])>= date()
- WHERE [Week_Planning] >= Today()

But I always get syntax errors.

The date format set at the beginning of the script is:

SET DateFormat='DD-MM-YYYY';

I add the 'where' statement after the FROM statement, that is correct right? For example:

SQL SELECT

     [Week] as [Week_Planning],
     [ProjectID] as [ProjectID_Planning],
     [PlanningID] as [PlanningID_Planning],
     [PersonnelID] as [EmployeeNumber_Planning],
     [Hours] as [Hours_Planning]

FROM tblPlanning
WHERE [Week_Planning] >= Date(Today());

Thanks again for any help, of you need additional information, just let me know.

Michelle.

Re: Importing a table using a date filter

Gysbert_Wassenaar — Tue, 23 Apr 2013 12:23:57 GMT

SQL SELECT

     [Week] as [Week_Planning],
     [ProjectID] as [ProjectID_Planning],
     [PlanningID] as [PlanningID_Planning],
     [PersonnelID] as [EmployeeNumber_Planning],
     [Hours] as [Hours_Planning]

FROM tblPlanning
WHERE [Week_Planning] >= Date();

Re: Importing a table using a date filter

Tue, 23 Apr 2013 12:32:35 GMT

Hey Gysbert:

I tried your suggestion, this time not with a syntax error:

ErrorSource: Microsoft Access Database Engine, ErrorMsg: No value given for one or more required parameters.

Best regards,

Michelle

Re: Importing a table using a date filter

Tue, 23 Apr 2013 13:03:15 GMT

You have two options

1/ Use the preceding load, which will enable you to use the QlikView functionality

LOAD

Where Date > Today()

;

LOAD ID,

Date;

SQL SELECT *

FROM Data

;

2/ add the where clause in the SQL and use Access functionality. Access syntax requires a lot of brackets see below example

LOAD ID,

Date;

SQL SELECT *

FROM Data

WHERE (((Date)>Now()));

Re: Importing a table using a date filter

Wed, 24 Apr 2013 07:26:14 GMT

Hello all,

Many thanks, especially to zohaibirshad, the statement 'WHERE (((Date)>Now()));' did what I was looking for!

I also tried with a little less brackets, and that seems to work too: 'where [Date]>Now();'

Michelle