Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 ,
WEEKFROM
(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
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
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