calendar loading problem

[arulsettu]

hi i created a calendar like this

Calendar:

LOAD Distinct

  OrderDate,

  Year(OrderDate) as Year,

  Month(OrderDate) as Month,

  Date(Monthstart(OrderDate), 'MMM-YYYY') as YearMonth,

  'Q' & Ceil(Month(OrderDate)/3) as quarter,

  Dual(Year(OrderDate) & '-Q' & Ceil(Month(OrderDate)/3), Year(OrderDate) & Ceil(Month(OrderDate)/3)) as YearQtr,

  Week(OrderDate) as Week

resident Sales_SalesOrderHeader;

when i reload script it shows error table not found.

i already loaded the database like this

ODBC CONNECT32 TO [MS Access Database;DBQ=E:\sample database\AdventureWorks.accdb];

what is my mistake? any help?

[maxgro · Accepted solution]

you're trying to build the Calendar table using a previous loaded table, Sales_SalesOrderHeader

but Sales_SalesOrderHeader doesn't exists

to ckech this, add the statement "exit script" in your script, reload your doc and use table viewer (CTRL-T) to check the tables

.......... 

exit script;

Calendar:

LOAD Distinct

  OrderDate,

  Year(OrderDate) as Year,

  Month(OrderDate) as Month,

  Date(Monthstart(OrderDate), 'MMM-YYYY') as YearMonth,

  'Q' & Ceil(Month(OrderDate)/3) as quarter,

  Dual(Year(OrderDate) & '-Q' & Ceil(Month(OrderDate)/3), Year(OrderDate) & Ceil(Month(OrderDate)/3)) as YearQtr,

  Week(OrderDate) as Week

resident Sales_SalesOrderHeader;

[luis_pimentel]

With the connection string

ODBC CONNECT32 TO [MS Access Database;DBQ=E:\sample database\AdventureWorks.accdb];

you are just connecting to the database, but not actually downloading any table.

Before creating the Calendar, you have to load your Sales_SalesOrderHeader table.

Sales_SalesOrderHeader

LOAD

     *

FROM yourtablename

Calendar

LOAD

     ......

Resident Sales_SalesOrderHeader;

[arulsettu]

again same erorr

LOAD OrderDate;

SQL SELECT OrderDate

FROM Sales_SalesOrderHeader;