Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello all.
I have been trying to get a preceding load to work with a connection to an Access DB. I have so far not been able to get this to work. What I am trying to do is create a reference field that defines the order in which months should appear in a graph. The fiscal year begins in October so in the graph I want October to appear first on the x-axis then November, December, etc..
Below is my current script. It does load the database data but doesn't seem to do anything with the LOAD statement.
ODBC CONNECT32 TO [MS Access Database;DBQ=J:\Fleet\Reports\ScoreCard\New_Scorecard\Scorecard.accdb];
SQL SELECT *
FROM tblScoreCardByWeek_Data_FiscalYears;
LOAD *,
sMonth,
if(sMonth=10,1,
if(sMonth=11,2,
if(sMonth=12,3,
if(sMonth=1,4,
if(sMonth=2,5,
if(sMonth=3,6,
if(sMonth=4,7,
if(sMonth=5,8,
if(sMonth=6,9,
if(sMonth=7,10,
if(sMonth=8,11,
if(sMonth=9,12
)))))))))))) as FISCAL_Month_ID;
I think we were adding sMonth using * and again with sMonth.... Try this
Table:
LOAD *,
sMonth,
if(sMonth=10,1,
if(sMonth=11,2,
if(sMonth=12,3,
if(sMonth=1,4,
if(sMonth=2,5,
if(sMonth=3,6,
if(sMonth=4,7,
if(sMonth=5,8,
if(sMonth=6,9,
if(sMonth=7,10,
if(sMonth=8,11,
if(sMonth=9,12
)))))))))))) as FISCAL_Month_ID
Resident TempTable;
I think you got the order incorrect... It should be like this
LOAD ....
SELECT ....
FROM ....
So try this:
ODBC CONNECT32 TO [MS Access Database;DBQ=J:\Fleet\Reports\ScoreCard\New_Scorecard\Scorecard.accdb];
LOAD *,
sMonth,
if(sMonth=10,1,
if(sMonth=11,2,
if(sMonth=12,3,
if(sMonth=1,4,
if(sMonth=2,5,
if(sMonth=3,6,
if(sMonth=4,7,
if(sMonth=5,8,
if(sMonth=6,9,
if(sMonth=7,10,
if(sMonth=8,11,
if(sMonth=9,12
)))))))))))) as FISCAL_Month_ID;
SQL SELECT *
FROM tblScoreCardByWeek_Data_FiscalYears;
Hi Sunny.
I did try tinkering with the order of the statement but when I order it as you suggested I receive the following error.
ODBC read failed
SQL SELECT *
FROM tblScoreCardByWeek_Data_FiscalYears
Do you still get the same error with Resident Load
ODBC CONNECT32 TO [MS Access Database;DBQ=J:\Fleet\Reports\ScoreCard\New_Scorecard\Scorecard.accdb];
TempTable:
SQL SELECT *
FROM tblScoreCardByWeek_Data_FiscalYears;
Table:
LOAD *,
sMonth,
if(sMonth=10,1,
if(sMonth=11,2,
if(sMonth=12,3,
if(sMonth=1,4,
if(sMonth=2,5,
if(sMonth=3,6,
if(sMonth=4,7,
if(sMonth=5,8,
if(sMonth=6,9,
if(sMonth=7,10,
if(sMonth=8,11,
if(sMonth=9,12
)))))))))))) as FISCAL_Month_ID
Resident TempTable;
DROP Table TempTable;
if you remove the preceeding load , does it read the data without any error?
I get a different error with Resident Load.
Field names must be unique within table
Table:
LOAD *,
sMonth,
if(sMonth=10,1,
if(sMonth=11,2,
if(sMonth=12,3,
if(sMonth=1,4,
if(sMonth=2,5,
if(sMonth=3,6,
if(sMonth=4,7,
if(sMonth=5,8,
if(sMonth=6,9,
if(sMonth=7,10,
if(sMonth=8,11,
if(sMonth=9,12
)))))))))))) as FISCAL_Month_ID
Resident TempTable
LOAD *,
sMonth,
if(sMonth=10,1,
if(sMonth=11,2,
if(sMonth=12,3,
if(sMonth=1,4,
if(sMonth=2,5,
if(sMonth=3,6,
if(sMonth=4,7,
if(sMonth=5,8,
if(sMonth=6,9,
if(sMonth=7,10,
if(sMonth=8,11,
if(sMonth=9,12
)))))))))))) as FISCAL_Month_ID
Resident TempTable
I think we were adding sMonth using * and again with sMonth.... Try this
Table:
LOAD *,
sMonth,
if(sMonth=10,1,
if(sMonth=11,2,
if(sMonth=12,3,
if(sMonth=1,4,
if(sMonth=2,5,
if(sMonth=3,6,
if(sMonth=4,7,
if(sMonth=5,8,
if(sMonth=6,9,
if(sMonth=7,10,
if(sMonth=8,11,
if(sMonth=9,12
)))))))))))) as FISCAL_Month_ID
Resident TempTable;
Sunny we can use Pick(Match()) as well right? Extending to few replies may be try this?
SET vAccessDBPath = 'J:\Fleet\Reports\ScoreCard\New_Scorecard\Scorecard.accdb';
ODBC CONNECT32 TO [MS Access Database;DBQ=J:\Fleet\Reports\ScoreCard\New_Scorecard\Scorecard.accdb];
Table1:
SQL SELECT *
FROM $(vAccessDBPath)."tblScoreCardByWeek_Data_FiscalYears";
NoConcatenate
FINAL:
LOAD *,
Pick(Match(sMonth, '10','11','12','1','2','3','4','5','6','7','8','9'), 1,2,3,4,5,6,7,8,9,10,11,12) AS FISCAL_Month_ID
Resident Table1;
DROP TABLE Table1;
I guess this is fine tuning, but the main question here was why did the preceding load error out... We can further fine tune your script to this
Pick(sMonth, 4,5,6,7,8,9,10,11,12,1,2,3) as FISCAL_Month_ID