Table not found error after inner join..help needed

[#1]

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

[stephencredmond · Accepted solution]

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