10 Replies Latest reply: Feb 9, 2011 5:50 PM by Miguel Angel Baeyens de Arce RSS

    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]

       

        • Using today() function
          Miguel Angel Baeyens de Arce

          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

              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
              Luca Cavallari

              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

                  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 de Arce

                      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