Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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.
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.
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;
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.