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;

[Yaakov_kris]

Thanks. What do you mean explicitly? These tables are generated each time that I run the code

[Øystein_Kolsrud]

I'll give you an example. Imagine that you have the following simple script:

For i = 1 to 3
  NoConcatenate Load Rand() as X AutoGenerate 1;
Next;

This will result in three tables all of which will get a name set by the engine. You don't really have any control of what those names will be, but you'll be able to see them in for instance the data model viewer. Now if you for some reason need to refer to those tables in your script, then the engine will allow that, but there is no guarantee that the names are preserved between engine releases. Or even between reloads. If you change the script, then you might unknowingly also make the engine change the naming of the table. In my case I happen to get the table names "AutoGenerate(1)", "AutoGenerate(1)-1" and "AutoGenerate(1)-2". If I change the script to "...as X Autogenerte 2;" instead then the name chosen by the engine will become "AutoGenerate(2)", "AutoGenerate(2)-1" and "AutoGenerate(2)-2". The engine has simply picked something according to its preferences. The only thing that is important for the engine is that the names are unique.

Now, to make names deterministic I can explicitly state the names of the tables in my script. It's OK to use variables in the names so I can do like this:

For i = 1 to 3
  MyTableName_$(i):
  NoConcatenate Load Rand() as X AutoGenerate 1;
Next;

Now the table names will become "MyTableName_1", "MyTableName_2" and "MyTableName_3" and these name will not change if I change other parts of the script. They are also guaranteed to be preserved across different versions of Qlik Sense, so this is what I recommend that you do and is what I mean by "Make sure you explicitly name the tables if you need to refer to them.".

[Øystein_Kolsrud]

Here is a support article on the topic: https://community.qlik.com/t5/Knowledge/Engine-auto-table-naming-process-is-not-working/ta-p/1884589

[Yaakov_kris]

Thanks! When I say that tables are generated within the loop I mean the following: I start the loop with table name vTableName = 'Current_Stock'  and vTableNumber=0, and then at the end of the loop I update as follows:

LET vTableNumber = vTableNumber + 1;

LET vTableName = 'Current_Stock' & '-' & '$(vTableNumber)';

Does it create an error after update?

[Øystein_Kolsrud]

No, if you use that variable as a table name, then it should work just fine. But in your case it was a table called "Ten_Days_Car_Fleet_Table-1" it errored out on, right?