Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Importing a table using a date filter

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

1 Solution

Accepted Solutions
Not applicable

Re: Importing a table using a date filter

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()));

9 Replies
Not applicable

Re: Importing a table using a date filter

Can you try:

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

Matt

Not applicable

Re: Importing a table using a date filter

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

Not applicable

Re: Importing a table using a date filter

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

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


Not applicable

Re: Importing a table using a date filter

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

Not applicable

Re: Importing a table using a date filter

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

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();


talk is cheap, supply exceeds demand
Not applicable

Re: Importing a table using a date filter

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

Not applicable

Re: Importing a table using a date filter

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()));

Not applicable

Re: Importing a table using a date filter

Hello all,

Community Browser