Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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()));
Can you try:
WHERE [Week_Planning] >= Date(Today()) ;
Matt
Sorry, just realized that likely will not work, can you send an example of the [Week] field?
If the week field returns a number, 1 - 52, try this:
WHERE [Week_Planning] >= week(today()) ;
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
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.
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();
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
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()));
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