topic Load only today's date information in QlikView

Load only today's date information

Fri, 20 Apr 2012 17:07:35 GMT

Please help me on what seems to be a really simple task.

So I only want to load todays date. Date = todays date

DaysFilledToday:

Qualify *;

ODBC CONNECT TO [MS Access Database;DBQ=G:\locumspm\BRT Team\Data Shack\CHLT DataShack DaysFilled.accdb];

LOAD ID,

Date,

month(Date) as MonthDate,

year(Date) as YearDate,

if((today()-Date)<62,1,0) as EightWeekDaysFilledHistory,

Division,

Team,

Specialty,

`Job Specialty`,

`Assignment Status`,

`Provider JDE #`,

`Cost Center #`,

`Provider Name`,

`Provider Specialty`,

`Job #`,

`Asgn From`,

`Job Type`,

`Provider Status`,

`Asgn To`,

Recruiter,

`Client Dev`,

`Asgn Prv Rep`,

`Asgn Clt Rep`,

`Client Name`,

`Client JDE#`,

`Buyer Status`,

`New Provider`,

`WorkSite Name`,

`WorkSite State`,

Month,

monthend(Month) as MonthEnd,

Days;

SQL SELECT *

FROM `Days Filled`;

store DaysFilled into QVDs\DaysFilledToday.qvd;

drop table DaysFilledToday;

(qvd);

Thank you in advance.

Re: Load only today's date information

jonathandienst — Fri, 20 Apr 2012 17:17:10 GMT

One really simple way would be:

     `WorkSite State`,
     Month,
     monthend(Month) as MonthEnd,
     Days
     Where Floor(Date) = Today();
SQL SELECT *
FROM `Days Filled`;

The downside is that the date filter is on the client, so the SQL query will return everything first. If the dataset is small, that is no problem

Regards

Jonathan

Re: Load only today's date information

jonathandienst — Fri, 20 Apr 2012 17:20:51 GMT

The other way, for larger data sets:

Let zToday = Date(Today(), 'YYYY-MM-DD');
...
...
SQL SELECT *
FROM `Days Filled`
WHERE Date = '$(zToday)';

The date format (YYYY-MM-DD) must match the date format of your database, so adjust if necessary.

Regards

Jonathan

Re: Load only today's date information

Fri, 20 Apr 2012 17:40:58 GMT

is there any way to get the maximum date without doing a group by?

Re: Load only today's date information

jonathandienst — Sat, 21 Apr 2012 06:13:39 GMT

I don't understand the question. Please provide more information.

Regards

Jonathan

Re: Load only today's date information

Nicole-Smith — Mon, 23 Apr 2012 13:13:33 GMT

You can use a where statement to do the job. Consider the following load script (I have also attached a .qvw file containing an example of this):

Table:

LOAD * INLINE [

Date, Number

4/1/12, 1

4/3/12, 3

4/15/12, 15

4/23/12, 23

]

WHERE Date = today();

Re: Load only today's date information

trey_bayne — Mon, 23 Apr 2012 14:16:19 GMT

I don't understand the connection between your first question, loading only Today's date, and second, finding the last date in a data set.

If you are trying to find the last date loaded in a data set you can load

max_date_table:

load Date ORDER BY Date ASC;

SQL SELECT *

FROM `Days Filled`;

Set vMaxDate = Peek('Date',-1,'max_date_table';

The value stored in the variable $(vMaxDate) would be the last date loaded in from the table max_date_table. Since you specified the Load order to be by the field "Date" in ASC (Ascending) order the latest date would be the last one.