4 Replies Latest reply: Feb 7, 2013 4:07 PM by Al Parikh RSS

    Sorting Eligibility Months in the correct order.

      Hello All;

       

      I have membership eligibility data in an Excel sheet. This data pertains to each eligible month from September 2012 onwards. Therefore, I have the following values for this attribute:
      Oct-2012
      Nov-2012
      Dec-2012

       

      However, in QlikView list box for this attribute, the values for Eligibility Month are not sorted in the right order because of which all chart objects display values for Oct-2012, followed by Dec-2012 and then Nov-2012.

       

      Is there an easy way for me to get this sort corrected?

       

      Thank you all.

       

      Regards;

       

      Al.

      Health Share of Oregon

        • Re: Sorting Eligibility Months in the correct order.
          Gysbert Wassenaar

          In the load script make dates from the month attribute using the date# function: date#(EligibleMonth,'MMM-YYYY') as EligibleMonth. Then you can sort the field numerically. That should give the correct order.

            • Re: Sorting Eligibility Months in the correct order.

              Hi Gysbert;

               

              I am afraid your solution did not work for me.

               

              I had the following commands originally:

               

              ODBC CONNECT TO [MS Access Database;DBQ=C:\DATAANALYSIS\Enrollmentdata.accdb];
              SQL SELECT MemberID,
                  EligiblilityMonth,
                  CaseID,
                  Effdate
              FROM EnrollmentCOMPLETE;

               

              I changed this to the following:


              ODBC CONNECT TO [MS Access Database;DBQ=C:\DATAANALYSIS\Enrollmentdata.accdb];
              SQL SELECT MemberID,
                  date#(EligibilityMonth,'MMM-YYYY') as EligibilityMonth,
                  CaseID,
                  Effdate
              FROM EnrollmentCOMPLETE;

               

              However, I now get a syntax error in date in query expression 'date#(EligibilityMonth,'Mmm-YYYY')'.

               

              The EligibilityMonth column in Access database is formatted as a TEXT column. Do you think that might be causing this problem?

               

              Thank yuo again.

               

              Regards;

               

              Al.

                • Re: Sorting Eligibility Months in the correct order.
                  Gysbert Wassenaar

                  No, the problem is you're trying to make Access execute Qlikview script.   Any sql is always executed by the dbms it's send to. Qlikview itself only hands over the sql statement and accepts the returned results. That also means that you can only use statements in the sql statement that the dbms understands. In this case you can use what's called a preceding load. The results from the SQL statement are piped to the load statement:

                   

                  ODBC CONNECT TO [MS Access Database;DBQ=C:\DATAANALYSIS\Enrollmentdata.accdb];

                   

                  MyTable:

                  load

                  MemberID,

                      date#(EligibilityMonth,'MMM-YYYY') as EligibilityMonth,

                      CaseID,

                      Effdate ;

                  SQL SELECT MemberID,

                      EligiblilityMonth,

                      CaseID,

                      Effdate

                  FROM EnrollmentCOMPLETE;