Tables are not found after an update

Yaakov_kris · ‎2022-02-20

Dear Qlik Community users,

After updating from the 2019 to 2021 version, we encountered the problem that some tables that should be created within the code are not found.

The tables are created within the loop where each newly created table gets the following name: "table_name-N" where N is a number that changes within the for-loop.

The code worked smoothly until the update, and now we get the error of the tables not being found.

I would be glad if you could help us with this! I attach the code code and the error below.

////Create table with all Vehicle Croups in  All Branches   Possibilities/////////////////
Shablone_Rent_Car_Fleet_Grounds1:
Load Distinct
num#([GroundID])AS [GroundID]
FROM $(Folder)/Dim_Rent_Car_Fleet_New.qvd](QVD);
 
 
Shablone_Rent_Car_Fleet_Vehicle1:
Load  Distinct
Vehicle_GroupID,
Vehicle_Group_Short_Name
FROM $(Folder)/Dim_Rent_Car_Fleet_New.qvd](QVD);
 
NoConcatenate
 
Shablone_Rent_Car_Fleet1:
LOAD [GroundID]
Resident Shablone_Rent_Car_Fleet_Grounds1;
 
Outer Join
 
Load Vehicle_GroupID,
Vehicle_Group_Short_Name
Resident Shablone_Rent_Car_Fleet_Vehicle1;
 
Drop Tables Shablone_Rent_Car_Fleet_Grounds1,Shablone_Rent_Car_Fleet_Vehicle1;
 
Store  Shablone_Rent_Car_Fleet1 INTO $(Folder)/Shablone_Rent_Car_Fleet1.qvd](QVD);
Drop Table Shablone_Rent_Car_Fleet1;
 
 
 
Dim_Rent_Car_Fleet_Pool_Daily1:
LOAD num#([GroundID])as GroundID_Pool,
     num#([Vehicle_GroupID]) as Vehicle_GroupID_Pool,
   LEFT(DATE#(MakeDate(YEAR([Mrr_Date]),NUM(MONTH([Mrr_Date])),NUM(DAY([Mrr_Date]))),'DD-MM-YYYY'),10)AS DateID_Pool,
   NUM#([Number_Of_Cars_For_Rent]) AS [Number_Of_Cars_For_Rent] ,
   NUM#([Number_Of_Cars_Occupied_In_Deal]) AS [Number_Of_Cars_Occupied_In_Deal],
   NUM#([Number_Of_Cars_Out_Of_Service]) AS  [Number_Of_Cars_Out_Of_Service]
FROM $(Folder)/Dim_Rent_Car_Fleet_For_Placement.qvd](QVD);
 
 
 
Dim_Rent_Car_Fleet_Pool_Daily2:
LOAD
GroundID_Pool as   GroundID_Pool_Daily,
Vehicle_GroupID_Pool   AS   Vehicle_GroupID_Pool_Daily,
DateID_Pool as DateID_Pool_Daily,
Sum([Number_Of_Cars_For_Rent]-[Number_Of_Cars_Occupied_In_Deal]-[Number_Of_Cars_Out_Of_Service])AS Number_Of_Cars_Available_Pool_Daily
Resident Dim_Rent_Car_Fleet_Pool_Daily1
GROUP BY GroundID_Pool,Vehicle_GroupID_Pool,DateID_Pool;
DROP Table  Dim_Rent_Car_Fleet_Pool_Daily1;
Store Dim_Rent_Car_Fleet_Pool_Daily2 into $(Folder)/Dim_Rent_Car_Fleet_Pool_Daily2.qvd](QVD);
Drop Table Dim_Rent_Car_Fleet_Pool_Daily2;
 
///Add Real Car Poll To Shablone/////
Dim_Rent_Car_Fleet_Pool_Daily3:
Load
'0'AS Number_Of_Cars,// Create Fictive car Poll to populate all row
 Date(Today(),'DD-MM-YYYY') AS DateID_Pool_Daily,/// Create Fictive date today (all Part Work on Current Day ONLY)
NUM#(Vehicle_GroupID)  AS  Vehicle_GroupID_Pool_Daily ,
Vehicle_Group_Short_Name,
GroundID AS GroundID_Pool_Daily
FROM $(Folder)/Shablone_Rent_Car_Fleet1.qvd](QVD);
 
Left Join
 
LOAD
   GroundID_Pool_Daily,
   Vehicle_GroupID_Pool_Daily,
   DateID_Pool_Daily,
   Number_Of_Cars_Available_Pool_Daily
From $(Folder)/Dim_Rent_Car_Fleet_Pool_Daily2.qvd](QVD); ;
 
 
Store Dim_Rent_Car_Fleet_Pool_Daily3 Into $(Folder)/Dim_Rent_Car_Fleet_Pool_Daily3.qvd](QVD);
Drop Table Dim_Rent_Car_Fleet_Pool_Daily3;
 
/// TABLE BELOW Have all posible combination of Ground to Vehicle Type with real if exist in real  pool ,or 0 if din't exists in real pool ////
Dim_Rent_Car_Fleet_Pool_Daily:
Load
GroundID_Pool_Daily,
Vehicle_GroupID_Pool_Daily,
Vehicle_Group_Short_Name as Vehicle_Group_Short_Name_Pool_Daily,
DateID_Pool_Daily,
'Till_High_Noon' AS Time_Group,
//IF (Left(Time(Now(),'hh:mm:ss'),2) >= '12', 'After_Mid_Day', 'Till_High_Noon') AS Time_Group,
IF(IsNull(Number_Of_Cars_Available_Pool_Daily)=-1,Number_Of_Cars,Number_Of_Cars_Available_Pool_Daily) AS Current_Stock
From $(Folder)/Dim_Rent_Car_Fleet_Pool_Daily3.qvd](QVD);
 
 
 
 
/////WorK With rent Orders ///////
 
 
 
Take_Or_Return_Car_Pool_Daily1:
LOAD
    [ContractID]as ContractID_Car_Pool,
    Deal_TypeID,
    Deal_Type,
   SubField(KEY,'-',2) as Vehicle_GroupID_Pool_Daily,
   DATE(LEFT(MakeDate(LEFT(DateID,4),MID(DateID,5,2),RIGHT(DateID,2)),10),'DD-MM-YYYY') AS DateID_Pool_Daily,
   GroundID AS GroundID_Pool_Daily,
   Vehicle_Group_Short_Name AS Vehicle_Group_Short_Name_Pool_Daily,
   [Take_Or_Return] as Take_Or_Return_Car_Pool_Daily,
   [Time]  as Take_Or_Return_Time,
   IF(TIME([Time])<=TIME('12:00'),'Till_High_Noon','After_Mid_Day')as Time_Group,
   IF([Take_Or_Return] =1 ,1,0) AS Vehicles_To_Give_Daily,
   IF([Take_Or_Return] =2 ,1,0) AS Vehicles_To_Take_Daily
 
FROM $(Folder)/Take_Or_Return1.qvd](qvd);
//Where [Take_Or_Return]=1 OR([Take_Or_Return]=2 AND Deal_TypeID=1) AND IsNull(ContractID)=0; // include return  cars only from Rent Contracts, cars to deliver - all
 
 
 
NoConcatenate
 
Take_Or_Return_Car_Pool_Daily2:
LOAD
  Vehicle_GroupID_Pool_Daily,
  Vehicle_Group_Short_Name_Pool_Daily,
  DateID_Pool_Daily,
  GroundID_Pool_Daily,
  Time_Group,
Sum(Vehicles_To_Give_Daily) AS Vehicles_Less,
Sum(Vehicles_To_Take_Daily) AS Vehicles_Add
Resident Take_Or_Return_Car_Pool_Daily1
WHERE DateID_Pool_Daily <= Date(Today() + 7 , 'DD-MM-YYYY') AND IsNull(ContractID_Car_Pool)=0
Group By Vehicle_GroupID_Pool_Daily, Vehicle_Group_Short_Name_Pool_Daily, DateID_Pool_Daily, GroundID_Pool_Daily, Time_Group;
 
Drop Table Take_Or_Return_Car_Pool_Daily1;
 
 
 
// הזמנות לעכשיו
 
 
 
// סיום הזמנות לעכשיו
 
 
LET vRunner = 1;
LET vCheckFirst = 1;
LET vDate = Date(Today(),'DD-MM-YYYY');
LET vTableNumber = 0;
LET vTableName = 'Current_Stock';
 
//            IF Left(Time(Now(),'hh:mm:ss'), 2) >= '12' THEN
//                            SET vTime_Group = 'After_Mid_Day';
//            ELSE
                                SET vTime_Group = 'Till_High_Noon';
//     END IF;
   
    
 
Do While vRunner <= 7
 
                IF $(vCheckFirst) = 1 THEN
   
                NoConcatenate
 
 
                Current_Stock1: // טבלה כוללת
                LOAD
      GroundID_Pool_Daily,
      Vehicle_GroupID_Pool_Daily,
      Vehicle_Group_Short_Name_Pool_Daily,
      DateID_Pool_Daily AS DateID,
      Time_Group,
      Current_Stock
                  Resident Dim_Rent_Car_Fleet_Pool_Daily;
      LET vCheckFirst = 0;
     
    LEFT JOIN (Current_Stock1)
   
   LOAD   // נתן לטבלה את מצב ההזמנות ע"י ג'וין
                                GroundID_Pool_Daily,
                                Vehicle_GroupID_Pool_Daily,
                                Vehicle_Group_Short_Name_Pool_Daily,
                                DateID_Pool_Daily AS DateID,
                                Time_Group,
                                Vehicles_Add - Vehicles_Less AS Vehicles_Delta
                Resident Take_Or_Return_Car_Pool_Daily2
    WHERE DateID_Pool_Daily = '$(vDate)' AND Time_Group = '$(vTime_Group)';
   
                NoConcatenate
   
  Current_Stock: // סוף חלק יום ראשון
 
    LOAD
               GroundID_Pool_Daily AS GroundID,
                                Vehicle_GroupID_Pool_Daily AS Vehicle_GroupID,
                                Vehicle_Group_Short_Name_Pool_Daily AS Vehicle_Group_Short_Name,
                                Date(DateID, 'DD-MM-YYYY') AS DateID,
                                Time_Group,
        Current_Stock + IF(IsNull(Vehicles_Delta) = -1, 0 , Vehicles_Delta) AS Current_Stock
    Resident Current_Stock1;
    Drop Table Current_Stock1;
   
    IF vTime_Group = 'After_Mid_Day' THEN
                LET vTime_Group = 'Till_High_Noon';
                LET vDate = Date(Today() + $(vRunner),'DD-MM-YYYY');
                LET vRunner = $(vRunner) + 1;
                ELSE
                LET vTime_Group = 'After_Mid_Day';
    END IF;
 
               
 
                ELSE
 NoConcatenate
   Current_Stock1: // מצב התחלתי של חלק יום אחרי
                LOAD
      GroundID,
      Vehicle_GroupID,
      Vehicle_Group_Short_Name,
      '$(vDate)' AS DateID,
      '$(vTime_Group)' AS Time_Group,
      Current_Stock
                  Resident '$(vTableName)'  ;
     
      
      
   LEFT JOIN (Current_Stock1)
  
   LOAD   // נתן לטבלה את מצב ההזמנות ע"י ג'וין
                                GroundID_Pool_Daily AS GroundID,
                                Vehicle_GroupID_Pool_Daily AS Vehicle_GroupID,
                                Vehicle_Group_Short_Name_Pool_Daily AS Vehicle_Group_Short_Name,
                                Date(DateID_Pool_Daily, 'DD-MM-YYYY') AS DateID,
                                Time_Group,
                                Vehicles_Add - Vehicles_Less AS Vehicles_Delta
                Resident Take_Or_Return_Car_Pool_Daily2
    WHERE DateID_Pool_Daily = '$(vDate)' AND Time_Group = '$(vTime_Group)';
   
    NoConcatenate
   
    Current_Stock:
    LOAD
                GroundID,
                                Vehicle_GroupID,
                                Vehicle_Group_Short_Name,
                                Date(DateID, 'DD-MM-YYYY') AS DateID,
                                Time_Group,
        Current_Stock + IF(IsNull(Vehicles_Delta) = -1, 0 , Vehicles_Delta) AS Current_Stock
    Resident Current_Stock1;
    Drop Table Current_Stock1;
               
                  
      
 
 
 
   
                IF vTime_Group = 'After_Mid_Day' THEN
                LET vTime_Group = 'Till_High_Noon';
                LET vDate = Date(Today() + $(vRunner),'DD-MM-YYYY');
                LET vRunner = $(vRunner) + 1;
                ELSE
                LET vTime_Group = 'After_Mid_Day';
    END IF;
    LET vTableNumber = vTableNumber + 1;
    LET vTableName = 'Current_Stock' & '-' & '$(vTableNumber)';
 
               
END IF;
 
 
LOOP
 
 
NoConcatenate
 
Current_Stock_Conc:
Load *
Resident [Current_Stock];
Concatenate
Load*
Resident [Current_Stock-1];
  Concatenate
Load*
Resident [Current_Stock-2];
Concatenate
Load*
Resident [Current_Stock-3];
Concatenate
Load*
Resident [Current_Stock-4];
Concatenate
Load*
Resident [Current_Stock-5];
Concatenate
Load*
Resident [Current_Stock-6];
Concatenate
Load*
Resident [Current_Stock-7];
Concatenate
Load*
Resident [Current_Stock-8];
//  Concatenate
Load*
Resident [Current_Stock-9];
Concatenate
Load*
Resident [Current_Stock-10];
Concatenate
Load*
Resident [Current_Stock-11];
Concatenate
  Load*
Resident [Current_Stock-12];
               Concatenate
                Load*
               Resident [Current_Stock-13];
 
 
Drop Tables [Current_Stock],  [Current_Stock-1], [Current_Stock-2], [Current_Stock-3], [Current_Stock-4], [Current_Stock-5], [Current_Stock-6], [Current_Stock-7], [Current_Stock-8], [Current_Stock-9],
[Current_Stock-10], [Current_Stock-11], [Current_Stock-12], [Current_Stock-13];
 
 
 
 
NoConcatenate
Timed_Car_Pool:
LOAD
                                GroundID&'-'& Vehicle_GroupID&'-'&Vehicle_Group_Short_Name&'-'& DateID &'-'&Time_Group&'-'& 'All' AS Kye_Timed_Car_Poll_Rent,
                GroundID,
                                Vehicle_GroupID,
                                Vehicle_Group_Short_Name,
                                Year(DateID)& Right('0'&Num(Month(DateID)),2)&RIGHT('0'&Num(Day(DateID)),2) AS DateID,
                                Time_Group,
        'All' AS Running_Deal_Type,
        Current_Stock AS Timed_Expected_Car_Pool
    Resident Current_Stock_Conc;
    Drop Table Current_Stock_Conc;
               
Drop Table Dim_Rent_Car_Fleet_Pool_Daily;
Drop Table Take_Or_Return_Car_Pool_Daily2;

General Question

Script