4 Replies Latest reply: May 31, 2011 9:57 AM by khetienne RSS

    Cannot filter on Date

      Hi

       

      It's been a while since I used QV, and I am quite rusty!

       

      I have an excel file that ( for this example) contains 2 fields, the date and a site derscription

       

      So, I load 'Date' and 'Site', as a 'tempMain', , reload as 'Main' to allow the ORDER BY and then use a date script I found on the web to generate dates from the earliest date in the 'tempMain' table

       

      using...

      MasterCalendar:

      load

      load TempDate as Date,

      week(TempDate) as Week,

      Year(TempDate) as Year,

      ...etc

       

      Now, I create two tables, linked by the key field 'Date'

       

      BUT

       

      When I select "Year" , ALl my "sites are deselected, but in theory, if I select 2011 in Year, the key should link back to all the dates in the Main that are in 2011

       

      ... shoudl it not..?..!

       

      Assistance please!

        • Cannot filter on Date
          Dennis Hoogenboom

          Most of the time this means that your 2 date fields are not the same format.

          Try to find it in this.

           

          If you can share your QV-document I will take a look.

          • Cannot filter on Date
            Erich Shiino

            you can press Ctrl+T to check the links between tables.

            On your main table you have TempDate or Date as a date field?

            I guess it's not properly linked.

            Maybe, you could use just :

            load

             

             

            load TempDate,

             

            week(TempDate) as Week,

             

            Year(TempDate) as Year,

            etc

             

            Hope it helps,

             

            Erich

            • Cannot filter on Date
              Ali Hijazi

              if your date column in the excel sheet contains values with time then if you use the num function to get the numeric representation of your date then it will give you a decimal value

              whereas if you use the num with the TempDate of the master calendar it will give you an integer

              to solve this problem use the following on the column of the excel sheet date(floor(columnName))

               

              the floor function takes the integer part of the date and the date() function gives you the date format that can be linked with the TempDate of the master calendar

              • Cannot filter on Date

                Thanks All. Every response was useful, in that it told me what to looko for and gave me the solution