topic Using today() function in QlikView

Using today() function

qw_johan — Tue, 08 Feb 2011 14:13:14 GMT

I am joining two tables using "left join load…" which works good.
I then want to filter out certain rows that are old using a "where" statement but
I keep getting different results using the following code:

where date_t >= Date#(Today(), 'yyyymmdd') OR date_t = ''

and

where date_t >= '20110208' OR date_t =''

…why is this? I don't understand.
Shouldn't I get the same results? Doesn't today() actually mean today?
Using today() I also get rows with dates from January (30^th, 23^rd, 9^th…)..I only expect to get rows from Feb 8^th and later (which I do get if I enter the real date).

My tables look like this:

EMP:

LOAD

@2 as id_nr,

@4 as date_f,

@5 as date_t

FROM

emp.txt(txt, codepage is 1252, no labels, delimiter is '|', msq, header is 8 lines)where @5>='20110208' OR @5='';

;

//where @5 >= Date#(Today(1), 'yyyymmdd') OR @5 = '';

//where @5>='20110208' OR @5='';

LEFT JOIN LOAD

@2 as id_nr,

@3 as last_name,

@4 as first_name

FROM

pers.txt(txt, codepage is 1252, no labels, delimiter is '|', msq, header is 8 lines);

I really want to avoid having to "hard code" a date in my script.

Any help appreciated.[:D]

Using today() function

Miguel_Angel_Baeyens — Tue, 08 Feb 2011 14:21:02 GMT

Hello Johan,

At a first glance, I'd say you need to quote the date function to get it working since you are passing a string:

LET vLoadDate = Date#(Today(1), 'YYYYMMDD'); EMP:LOAD@2 as id_nr,@4 as date_f,@5 as date_t..FROMemp.txt(txt, codepage is 1252, no labels, delimiter is '|', msq, header is 8 lines)where @5 >= '$(vLoadDate)' OR @5 = '';

Hope that helps.

Using today() function

qw_johan — Tue, 08 Feb 2011 17:12:22 GMT

Hi, and thanks for helping me.
I am sorry to say that your code didn't fix it. Instead it gave me an even more strange result.

Result: 5422
where @5>='$(vLoadDate)' OR @5='';

Result: 3722
where @5 >= Date#(Today(1), 'yyyymmdd') OR @5 = '';

Result: 3716
where @5>='20110208' OR @5='';

3716 I think is the accurate result of distinct id_nr.
These should all give the same result I think. Very strange to me.

Using today() function

Miguel_Angel_Baeyens — Tue, 08 Feb 2011 18:20:04 GMT

Hi,

It's likely the variable is not being formatted properly, try the following instead

LET vLoadDate = Date(Date#(Today()), 'YYYYMMDD')

And then the load script as I posted above.

Hope that helps.

Using today() function

qw_johan — Tue, 08 Feb 2011 23:21:21 GMT

I did the change but unfortunately it didn't help.
I still get 4 dates in January 2011 (31, 23, 11, 09) which is REALLY strange to me.

Why does 20110208 give a different result than using today()???

Could it be something else causing this...?

Using today() function

Miguel_Angel_Baeyens — Wed, 09 Feb 2011 07:08:45 GMT

I don't think so, since if the variable is returning the proper value, it has the same effect thatn to set manualle the date in the format you do.

First, check that the variable stores the right value (you can go to Settings, Variable Overview, and check for vLoadDate) with the right format. For today, it must be 20110209 (without quotes).

Second, I always quote strings (literals) when passed as parameters, it may not be the case (although you are actually quoting when you hardcode the date in the WHERE statement).

Regards.

Using today() function

qw_johan — Wed, 09 Feb 2011 08:43:21 GMT

When I look under Variable Overview I can see that the value is not stored properly.
vLoadDate has a value of 20110009 !?!?
This explains why I get hits on January. How can QlikView create a month that's '00'?

Thanks

Using today() function

Miguel_Angel_Baeyens — Wed, 09 Feb 2011 08:55:08 GMT

Hi,

In the document itself, create a text object and write

=Date(Today(), 'YYYYMMDD')

To see whether there is any problem with dates and formats in QlikView. If that shows "20110209" then use that in the variable an reload the excel spreadsheet according to the code write above.

But yes, that's something strange and the reason why the records are not being loaded properly.

Hope that helps.

Using today() function

lucas4bi — Wed, 09 Feb 2011 10:52:12 GMT

Hi,

first of all: try to use 'YYYYMMDD' instead of 'yyyymmdd' (caps on), because qlikview may interpretate mm as minutes, when MM is month.

then, why are you using the date#() function? isn't that to format text string? instead today() return a number value, did u try with the date() function?

Date(Today(),'YYYYMMDD').

let me know

Using today() function

qw_johan — Wed, 09 Feb 2011 21:50:59 GMT

Thanks for your help. Finally it is working.

Final code is:
LET vLoadDate = Date(Today(), 'YYYYMMDD');

and then after the load...
where @5>='$(vLoadDate)' OR @5='';

So what have I learned....I need to put the date in a variable and that I have to use quotes around variable, like '$(vLoadDate)'.
YYYYMMDD needs to be in capitals too.

But it's still a mystery to me why this doesn't work:
where @5>=Date(Today(), 'YYYYMMDD') OR @5='';

Again, thanks to everybody that help me.

Using today() function

Miguel_Angel_Baeyens — Wed, 09 Feb 2011 22:50:52 GMT

Hello Johan,

That WHERE statement is correct and it should work if the function Date() is evaluated properly. The following code works:

RawData:LOAD Date(Date('01/01/2010') + Rand() * 730, 'YYYYMMDD') AS DateAUTOGENERATE 2000; // Just dummy data to compare with ValidData:LOAD *, RecNo() AS Dummy // Another field to not concatenate with previous tableRESIDENT RawDataWHERE Date < Date(Today(), 'YYYYMMDD'); // Here the date is being evaluated correctly DROP TABLE RawData;

In the example above, "Date" field as an actual date field (so numeric) and that allows not to quote the function. So to properly evaluate the date when it's a string and passed as valid value for a field, it would be

LET vToday = Date(Today(), 'YYYYMMDD'); RawData:LOAD Text(Date(Date('01/01/2010') + Rand() * 730, 'YYYYMMDD')) AS DateAUTOGENERATE 2000; ValidData:LOAD *, RecNo() AS DummyRESIDENT RawDataWHERE Date < '$(vToday)'; DROP TABLE RawData;

Hope that helps