Table not found error after inner join..help needed

Fri, 10 Jun 2011 17:50:17 GMT

Hello Every ,

I am using some base tables and then some derived tables. in the last inner join, i get the error that table not found even though the table has been successfully create, which i can verify from the logfile too. As I have identified below, the error occurs only in the last statement. I have also tried to qualify the tables after calandar table so that each table has a different name but then it gives out of memory error. Can some one please help

Planning_View:
LOAD PLAN_WEEK as PV_PLAN_WEEK,
     ITEM_CODE AS PV_MM_ITEM_CODE,
     FORE_WEEK as PV_FORE_WEEK,
     ITEMDEMAND
     FROM

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

Calandar:
LOAD PERIOD ,
WEEK

FROM

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

Plan_Calandar:
LOAD PERIOD AS PV_PLAN_PERIOD,
WEEK AS PV_PLAN_WEEK
RESIDENT Calandar;

INNER JOIN (Plan_Calandar)

LOAD PV_PLAN_WEEK

RESIDENT Planning_View;

Fore_Calandar:
LOAD PERIOD AS PV_FORE_PERIOD,
WEEK AS PV_FORE_WEEK
RESIDENT Calandar;

INNER JOIN (Fore_Calandar)

LOAD PV_FORE_WEEK

RESIDENT Planning_View;

Max_Plan_Calandar:
LOAD PV_PLAN_PERIOD,
MAX(PV_PLAN_WEEK) AS PV_PLAN_WEEK
RESIDENT Plan_Calandar
GROUP BY PV_PLAN_PERIOD;

Max_Fore_Calandar:
LOAD PV_FORE_PERIOD,
MAX(PV_FORE_WEEK) AS PV_FORE_WEEK
RESIDENT Fore_Calandar
GROUP BY PV_FORE_PERIOD;

Max_Data:
NOCONCATENATE
LOAD PV_PLAN_WEEK,
PV_FORE_WEEK,
ITEMDEMAND
RESIDENT Planning_View;

INNER JOIN (Max_Data)

LOAD PV_PLAN_PERIOD,
PV_PLAN_WEEK
RESIDENT Max_Plan_Calandar;

//TABLE NOT FOUND ERROR HERE

INNER JOIN (Max_Data)

LOAD PV_FORE_PERIOD,
PV_FORE_WEEK
RESIDENT Max_Fore_Calandar;

//TABLE NOT FOUND ERROR HERE

DROP TABLE Plan_Calandar;
DROP TABLE Fore_Calandar;
DROP TABLE Max_Plan_Calandar;
DROP TABLE Max_Fore_Calandar;

-br

Arif

Table not found error after inner join..help needed

stephencredmond — Fri, 10 Jun 2011 21:48:07 GMT

Hi Arif,

Your problem is that you are loading like this:

Plan_Calandar: LOAD PERIOD AS PV_PLAN_PERIOD, WEEK AS PV_PLAN_WEEK RESIDENT Calandar; INNER JOIN (Plan_Calandar) LOAD PV_PLAN_WEEK RESIDENT Planning_View; ... Max_Plan_Calandar: LOAD PV_PLAN_PERIOD, MAX(PV_PLAN_WEEK) AS PV_PLAN_WEEK RESIDENT Plan_Calandar GROUP BY PV_PLAN_PERIOD;

When you load the same number and named fields, QlikView will automatically concatenate. Therefore, the values that you think will be loaded into a new table called "Max_Plan_Calendar" will actually be concatenated onto "Plan_Calendar" so you will get that table not found error.

A simple way to fix this is:

Max_Plan_Calandar:

NoConcatenate

LOAD PV_PLAN_PERIOD,

MAX(PV_PLAN_WEEK) AS PV_PLAN_WEEK

RESIDENT Plan_Calandar

GROUP BY PV_PLAN_PERIOD;

Obviously, at some stage, you need to drop/join some of these tables to prevent synthetic keys.

Regards,

Stephen

topic Table not found error after inner join..help needed in QlikView

Table not found error after inner join..help needed

Table not found error after inner join..help needed