topic Re: Preceding Load with Access DB Connection in QlikView

Preceding Load with Access DB Connection

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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;

Re: Preceding Load with Access DB Connection

sunny_talwar — Wed, 08 Mar 2017 14:00:17 GMT

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;

Re: Preceding Load with Access DB Connection

Anonymous — Wed, 08 Mar 2017 14:02:41 GMT

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

Re: Preceding Load with Access DB Connection

sunny_talwar — Wed, 08 Mar 2017 14:11:09 GMT

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;

Re: Preceding Load with Access DB Connection

sasiparupudi1 — Wed, 08 Mar 2017 14:18:20 GMT

if you remove the preceeding load , does it read the data without any error?

Re: Preceding Load with Access DB Connection

Anonymous — Wed, 08 Mar 2017 14:18:36 GMT

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

Re: Preceding Load with Access DB Connection

sasiparupudi1 — Wed, 08 Mar 2017 14:20:08 GMT

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

Re: Preceding Load with Access DB Connection

sunny_talwar — Wed, 08 Mar 2017 14:20:41 GMT

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;

Re: Preceding Load with Access DB Connection

vishsaggi — Wed, 08 Mar 2017 14:37:42 GMT

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;

Re: Preceding Load with Access DB Connection

sunny_talwar — Wed, 08 Mar 2017 14:41:55 GMT

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

Re: Preceding Load with Access DB Connection

vishsaggi — Wed, 08 Mar 2017 14:44:39 GMT

Exactly.

Re: Preceding Load with Access DB Connection

Anonymous — Wed, 08 Mar 2017 15:01:01 GMT

That was the issue. I had read that this error sometimes points to a duplicated field name but I didn't understand how the field name was being duplicated. Thanks for the help!

I am also going to implement your Pick() solution. Much cleaner!