Sorting Eligibility Months in the correct order.

[#1]

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

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

[#3]

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.

[Gysbert_Wassenaar · Accepted solution]

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;

[#5]

Thank you very much for conveying that elementary piece of information. I am now able to see the right order of data set.

Regards;

Al.

Health Share of Oregon.