Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
qw_johan
Creator
Creator

Using today() function

Hi

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 (30th, 23rd, 9th…)..I only expect to get rows from Feb 8th 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]

10 Replies
Miguel_Angel_Baeyens

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.

qw_johan
Creator
Creator
Author

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.



Miguel_Angel_Baeyens

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.

qw_johan
Creator
Creator
Author

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...?

Miguel_Angel_Baeyens

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.

qw_johan
Creator
Creator
Author

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

Miguel_Angel_Baeyens

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.

lucas4bi
Partner - Creator
Partner - Creator

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

qw_johan
Creator
Creator
Author

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.