7 Replies Latest reply: Dec 28, 2011 4:05 AM by dan.ludlam RSS

    Load data depending on date

      Hi All,
      I'm new to Qlikview, I've had my training but I have a few more questions. I apologise in advance if this is not the place to be asking questions.
      I do not want to load all data, I only want to load data between two different date values.
      // Import STUDY table
      STUDY:
      LOAD STUDYID,
      VISITID,
      RISID,
      EXAMTYPEID,
      EXAMID,
      1
      as STUDYCOUNTER,
      STUDYDATE,
      STATUS,
      STUDYUID,
      BODYSITEMODIFIER,
      PRIORITY,
      INTERFACEID;
      SQL SELECT *
      FROM SolitonServer.dbo.STUDY;
      I'd like to change this statement to be something like 'FROM Soliton Server.dbo.STUDY where STUDYDATE > '12/10/2010 00:00:00' and STUDYDATE < '21/01/2011 00:00:00';
      I get an error when I try to use this statment. The error is below:
      The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
      Any help on how to fix this error would be muchly appreciated.
      Regards,
      Dan
        • Load data depending on date
          Sokkorn Cheav

          Hi Dan,

           

          Can you check data type for the field STUDYDATE. Does it is DateTime or Char? What is the formate for this field?

          or you can try where STUDYDATE > '12-10-2010' and STUDYDATE < '21-01-2011'

           

          Regards,

          Sokkorn Cheav

            • Load data depending on date

              Hi Sokkorn Cheav,

               

              In my SQL table the Data Type of the field STUDYDATE is 'datetime'. So the format is DD/MM/YYYY HH:MM:SS.

               

              Regards,

               

              Dan

                • Load data depending on date

                  Hi All,

                   

                  I've had some success now. Not sure what I was doing wrong but the following line seems to work fine now:

                   

                  FROM SolitonServer.dbo.STUDY where STUDYDATE > '12/11/2003 00:00:00' and STUDYDATE < '01/01/2011 00:00:00';

                   

                  Now I want to change these date values to be read from an xml file. I have the following script for a 'dates' table:

                   

                  // Start of [daterange.xml] LOAD statements
                  dates:
                  LOAD startdate
                  enddate
                  FROM [..\..\daterange.xml] (XmlSimple, Table is [dates]);
                  // End of [daterange.xml] LOAD statements

                   

                  So I thought I would be able to change my statement in the STUDY table to read:

                   

                  FROM SolitonServer.dbo.STUDY where STUDYDATE > startdate and STUDY.STUDYDATE < enddate;

                   

                  But I get the following error:

                   

                  Invalid column name 'enddate'.

                   

                  Any Ideas?

                   

                  Thanks,

                   

                  Dan

                    • Re: Load data depending on date
                      Sokkorn Cheav

                      Hi Dan,

                       

                      I think you forgot comma sign (,)

                      Look like this:

                      // Start of [daterange.xml] LOAD statements

                      dates:

                      LOAD

                      startdate,     //Maybe this point (,)

                      enddate

                      FROM [..\..\daterange.xml] (XmlSimple, Table is [dates]);

                      // End of [daterange.xml] LOAD statements

                       

                      Regards,

                      Sokkorn Cheav

                        • Re: Load data depending on date
                          John Witherspoon

                          One load statement can't refer to field names in completely different table.  So after loading the dates, I believe you need to do this:

                           

                          LET vStartDate = peek('startdate');
                          LET vEndDate   = peek('enddate');

                          ...

                          FROM SolitonServer.dbo.STUDY
                          WHERE STUDYDATE > '$(vStartDate)'
                            AND STUDYDATE < '$(vEndDate)';

                           

                          It is possible you will need to fiddle with the variables to get the correct format to match the studydate, but I'm thinking you won't have to as long as your XML source has them looking correct.

                            • Load data depending on date
                              Karl Pover

                              Going back to the out of range datetime value, are you sure the format is DD/MM/YYYY HH:MM:SS and not MM/DD/YYYY HH:MM:SS since you got the error when you used '21/01/2011 00:00:00'  and not when you used '01/01/2011 00:00:00'

                                • Re: Load data depending on date

                                  Thanks John and Karl,

                                   

                                  John, your solution with setting the dates as variables worked great.

                                   

                                  Karl, you were right about the date format, I think I was getting confused as the date was being displayed in DD/MM/YYYY in Qlikview but I assume that Qlikview was changing the format for me.

                                   

                                  Problem Solved, thanks again!

                                   

                                  Dan