Updated from 2016 to 2019 and the model doesn't work

[Yaakov_kris]

Qlik was updated from 2016 to 2019 version, and now we have the model that doesn’t work fully: the model itself works, but the loop doesn’t. These are tables that are not found:

////This Section is Identical to Running_Car_Pool_Rent Except no Where in [Running_Car_Orders_Special_Rent1] - all contracts and orders are calculated.
////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;

Rent_Car_Fleet_Running1:
LOAD
GroundID,
Vehicle_GroupID,
Sum([Number_Of_Cars_For_Rent]-[Number_Of_Cars_Occupied_In_Deal]-[Number_Of_Cars_Out_Of_Service]) AS [Number_Of_Cars_Available],
DATE#(LEFT(Mrr_Date,10) , 'DD-MM-YYYY') AS DateID
FROM $(Folder)/Dim_Rent_Car_Fleet_For_Placement.qvd](qvd)
Group By GroundID, Vehicle_GroupID,DATE#(LEFT(Mrr_Date,10) , 'DD-MM-YYYY');

Rent_Car_Fleet_Running2:
LOAD
GroundID,
Vehicle_GroupID,
Vehicle_Group_Short_Name
FROM $(Folder)/Shablone_Rent_Car_Fleet1.qvd]
(qvd);
Left Join

Load
GroundID,
Vehicle_GroupID,
Number_Of_Cars_Available,
DateID
Resident Rent_Car_Fleet_Running1;
Drop TABLE Rent_Car_Fleet_Running1;

NoConcatenate

Rent_Car_Fleet_Running:
Load
GroundID,
Vehicle_GroupID,
Vehicle_Group_Short_Name,
If(IsNull(DateID)=-1, Date#(Left(Now(),10),'DD-MM-YYYY') ,DateID)AS DateID,
If(IsNull(Number_Of_Cars_Available)=-1,0,Number_Of_Cars_Available)as Number_Of_Cars_Available
Resident Rent_Car_Fleet_Running2;

Store Rent_Car_Fleet_Running Into $(Folder)/Rent_Car_Fleet_Running.qvd](qvd);
Drop Tables Rent_Car_Fleet_Running2,Rent_Car_Fleet_Running;

Running_Car_Orders1:
LOAD
// ContractID,
// Flag_Is_Active_Contract,
// License_Plate_Number,
Count(ContractID) as Vehicles_Number,
Vehicle_Group_Short_Name,
GroundID,
Deal_Type,
Deal_TypeID,
DATE#(MakeDate(LEFT(DateID,4),MID(DateID,5,2),Right(DateID,2)),'DD-MM-YYYY') AS DateID,
// "Time",
Take_Or_Return,
SubField(KEY,'-',2)AS Vehicle_GroupID
FROM $(Folder)/Take_Or_Return1.qvd](qvd)
Group BY GroundID,KEY , Vehicle_Group_Short_Name,DateID ,Deal_Type,Deal_TypeID,Take_Or_Return;

Running_Car_Orders_Special_Rent1:
Load
DateID,
GroundID,
Vehicle_GroupID,
Vehicle_Group_Short_Name,
Take_Or_Return,
IF(Take_Or_Return=1,(Vehicles_Number*-1),Vehicles_Number) as Vehicles_Number
Resident Running_Car_Orders1 ;

Drop Table Running_Car_Orders1;

Running_Car_Orders_Special_Rent:
Load
DateID,
GroundID,
Vehicle_GroupID,
Vehicle_Group_Short_Name,
Sum(Vehicles_Number) AS Vehicles_Delta
RESIDENT Running_Car_Orders_Special_Rent1
Group BY DateID,GroundID,Vehicle_GroupID,Vehicle_Group_Short_Name;

Store Running_Car_Orders_Special_Rent Into $(Folder)/Running_Car_Orders_Special_Rent.qvd](qvd);

Drop Tables Running_Car_Orders_Special_Rent1,Running_Car_Orders_Special_Rent;

LET vToday_For_Rent_Date =Date(Date#(Left(Today(),10)),'DD-MM-YYYY');
LET vStop_Rent_Date= Date((Left(Today()+10,10)),'DD-MM-YYYY');

Do While vToday_For_Rent_Date<=vStop_Rent_Date

IF vToday_For_Rent_Date=Date(Date#(Left(Today(),10)),'DD-MM-YYYY') THEN

Rent_Running_Car_Fleet1:
Load
GroundID,
Vehicle_GroupID,
Vehicle_Group_Short_Name,
DateID,
Number_Of_Cars_Available
FROM $(Folder)/Rent_Car_Fleet_Running.qvd](qvd);

Left Join

Load
DateID,
GroundID,
Vehicle_GroupID,
Vehicle_Group_Short_Name,
Vehicles_Delta
FROM $(Folder)/Running_Car_Orders_Special_Rent.qvd](qvd);

Store Rent_Running_Car_Fleet1 INTO $(Folder)/Rent_Running_Car_Fleet1.qvd](qvd);

Drop Table Rent_Running_Car_Fleet1;

Else

Rent_Running_Car_Fleet1:
Load
DateID,
GroundID,
Vehicle_GroupID,
Vehicle_Group_Short_Name,
Number_Of_Cars_Available
From $(Folder)/End_Day_Car_Fleet.qvd](qvd);

Left Join

Load
DateID,
GroundID,
Vehicle_GroupID,
Vehicle_Group_Short_Name,
Vehicles_Delta
FROM $(Folder)/Running_Car_Orders_Special_Rent.qvd](qvd);

Store Rent_Running_Car_Fleet1 INTO $(Folder)/Rent_Running_Car_Fleet1.qvd](qvd);

Drop Table Rent_Running_Car_Fleet1;

End If;

NoConcatenate

Running_Car_Fleet2:
Load
GroundID,
Vehicle_GroupID,
Vehicle_Group_Short_Name,
DateID,
Number_Of_Cars_Available,
IF(IsNull(Vehicles_Delta)=-1,0 ,Vehicles_Delta) AS Vehicles_Delta
From $(Folder)/Rent_Running_Car_Fleet1.qvd](qvd);

NoConcatenate

Running_Car_Fleet3:
Load
GroundID,
Vehicle_GroupID,
Vehicle_Group_Short_Name,
DateID,
Sum(Number_Of_Cars_Available + Vehicles_Delta) AS Car_Fleet_Number
Resident Running_Car_Fleet2
Group By GroundID,Vehicle_GroupID,Vehicle_Group_Short_Name,DateID;

Drop Table Running_Car_Fleet2;

NoConcatenate

End_Day_Car_Fleet:
Load
GroundID,
Vehicle_GroupID,
Vehicle_Group_Short_Name,
Date((Num('$(vToday_For_Rent_Date)')+1),'DD-MM-YYYY') AS DateID,
Car_Fleet_Number AS Number_Of_Cars_Available
Resident Running_Car_Fleet3;

Store End_Day_Car_Fleet Into $(Folder)/End_Day_Car_Fleet.qvd](qvd);

NoConcatenate

Ten_Days_Car_Fleet_Table:
Load
GroundID,
Vehicle_GroupID,
Vehicle_Group_Short_Name,
DateID,
Car_Fleet_Number
Resident Running_Car_Fleet3;

Store Ten_Days_Car_Fleet_Table Into $(Folder)/Ten_Days_Car_Fleet_Table.qvd](qvd);

Drop Table End_Day_Car_Fleet;
Drop Table Running_Car_Fleet3;

//Drop Table Running_Car_Fleet3 ,Running_Car_Fleet2, Rent_Running_Car_Fleet1;

LET vToday_For_Rent_Date= Date(Date#(Num#(vToday_For_Rent_Date))+1,'DD-MM-YYYY');

Loop

//Drop Tables Rent_Running_Car_Fleet1;

NoConcatenate

Rent_Car_Fleet_Daily_Concatenate:
Load *
Resident [Ten_Days_Car_Fleet_Table];
Concatenate
Load*
Resident [Ten_Days_Car_Fleet_Table-1];
Concatenate
Load*
Resident [Ten_Days_Car_Fleet_Table-2];
Concatenate
Load*
Resident [Ten_Days_Car_Fleet_Table-3];
Concatenate
Load*
Resident [Ten_Days_Car_Fleet_Table-4];
Concatenate
Load*
Resident [Ten_Days_Car_Fleet_Table-5];
Concatenate
Load*
Resident [Ten_Days_Car_Fleet_Table-6];
Concatenate
Load*
Resident [Ten_Days_Car_Fleet_Table-7];
Concatenate
Load*
Resident [Ten_Days_Car_Fleet_Table-8];
// Concatenate
Load*
Resident [Ten_Days_Car_Fleet_Table-9];
Concatenate
Load*
Resident [Ten_Days_Car_Fleet_Table-10];

Store Rent_Car_Fleet_Daily_Concatenate Into $(Folder)/Rent_Car_Fleet_Daily_Concatenate.qvd](qvd);

Drop Table Rent_Car_Fleet_Daily_Concatenate;
//Drop Table End_Day_Car_Fleet;

Drop Tables [Ten_Days_Car_Fleet_Table],[Ten_Days_Car_Fleet_Table-1],[Ten_Days_Car_Fleet_Table-2],
[Ten_Days_Car_Fleet_Table-3],[Ten_Days_Car_Fleet_Table-4],[Ten_Days_Car_Fleet_Table-5],[Ten_Days_Car_Fleet_Table-6],[Ten_Days_Car_Fleet_Table-7],
[Ten_Days_Car_Fleet_Table-8],[Ten_Days_Car_Fleet_Table-9],[Ten_Days_Car_Fleet_Table-10];

NoConcatenate

Rent_Car_Fleet_Daily_All1:
Load
GroundID&'-'&Vehicle_GroupID&'-'&Vehicle_Group_Short_Name&'-'&DateID as Key_Rent_Car_Fleet_Daily_All,
GroundID,
Vehicle_GroupID,
Vehicle_Group_Short_Name,
Year(DateID)& Right('0'&Num(Month(DateID)),2)&RIGHT('0'&Num(Day(DateID)),2) AS DateID,
'All' As Running_Deal_Type,
Car_Fleet_Number as All_Running_Car_Fleet_Number
FROM $(Folder)/Rent_Car_Fleet_Daily_Concatenate.qvd](qvd);

NoConcatenate

Rent_Car_Fleet_Daily_All:
Load
GroundID&'-'&Vehicle_GroupID&'-'&Vehicle_Group_Short_Name&'-'&DateID&'-'&Running_Deal_Type as Key_Rent_Car_Fleet_Daily_All,
GroundID,
Vehicle_GroupID,
Vehicle_Group_Short_Name,
DateID,
Running_Deal_Type,
All_Running_Car_Fleet_Number
Resident Rent_Car_Fleet_Daily_All1;

Drop Table Rent_Car_Fleet_Daily_All1;

[PrashantSangle]

what is error?? when you run the script, what is error you are getting, can you post that?

[Yaakov_kris]

 

[Yaakov_kris]

20220209T082224.433+0200 0984  Load*

20220209T082224.433+0200 0985  Resident [Ten_Days_Car_Fleet_Table-1]

20220209T082224.433+0200      Error: Table 'Ten_Days_Car_Fleet_Table-1' not found

20220209T082224.450+0200      Execution Failed

20220209T082224.462+0200      Execution finished.

[PrashantSangle]

Are you sure you have such table in your script? i don't see table "Ten_Days_Car_Fleet_Table-1" in your posted script. and neither others 

Ten_Days_Car_Fleet_Table-2, Ten_Days_Car_Fleet_Table-3....Ten_Days_Car_Fleet_Table-10

Regards,

Prashant Sangle

[Yaakov_kris]

Yes,it should be created with in a while loop .

[PrashantSangle]

Hi,

try by replacing 

LET vToday_For_Rent_Date= Date(Date#(Num#(vToday_For_Rent_Date))+1,'DD-MM-YYYY'); 

with

LET vToday_For_Rent_Date = Date(Date#(vToday_For_Rent_Date,'DD-MM-YYYY')+1,'DD-MM-YYYY');

Regards,

Prashant Sangle

[Øystein_Kolsrud]

I'm not sure where that table named "Ten_Days_Car_Fleet_Table-1" is coming from (did you include everything in the script?), but it looks like an auto-generated named coming from the engine. You should avoid relying on those names in your script as they are not guaranteed to be preserved between releases. Those names are primarily intended for internal book-keeping in the engine.

In other words: if you need to refer to a table in the script, then make sure you explicitly name it.

[Yaakov_kris]

Hi, thanks. The tables are created within a while loop, and the code worked properly in qlik sense 2016, however, I started getting this problem of tables not found when we updated to qlik sense 2019. What can be the reason? The code worked properly before but the update.

[Øystein_Kolsrud]

I'm guessing the auto generated table names have changed between the two versions. Those names are not part of the API and should not be relied on, which is why you should never refer to such names in your script. Make sure you explicitly name the tables if you need to refer to them.