We had an update from 2019 to 2021, and now the mo... - Qlik Community

Yaakov_kris · ‎2022-02-20

We had an update from 2019 to 2021, and now the model doesn’t work, we don’t understand what’s wrong with it

13:50:51

Table 'Current_Stock-1' not found: NoConcatenate Current_Stock1: LOAD GroundID, Vehicle_GroupID, Vehicle_Group_Short_Name, '18-02-2022' AS DateID, 'Till_High_Noon' AS Time_Group, Current_Stock Resident 'Current_Stock-1'

and code:

////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,

//IF (Left(Time(Now(),'hh:mm:ss'),2) >= '12', 'After_Mid_Day', 'Till_High_Noon') AS Time_Group,

'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); // 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_Rent_Only:

LOAD

// GroundID&'-'& Vehicle_GroupID&'-'&Vehicle_Group_Short_Name&'-'& DateID &'-'&Time_Group&'-'& 'Rent' 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,

'Rent' AS Running_Deal_Type,

Current_Stock AS Timed_Expected_Car_Pool_Rent

Resident Current_Stock_Conc;

JOIN(Timed_Car_Pool_Rent_Only)

LOAD

// Kye_Timed_Car_Poll_Rent,

GroundID,

Vehicle_GroupID,

Vehicle_Group_Short_Name,

DateID,

Time_Group,

Running_Deal_Type,

Timed_Expected_Car_Pool

Resident Timed_Car_Pool;

Drop Table Current_Stock_Conc;

Drop Table Dim_Rent_Car_Fleet_Pool_Daily;

Drop Table Take_Or_Return_Car_Pool_Daily2;

Drop Table Timed_Car_Pool;

Timed_Car_Pool:

LOAD

GroundID&'-'& Vehicle_GroupID&'-'&Vehicle_Group_Short_Name&'-'& DateID &'-'&Time_Group&'-'& Running_Deal_Type AS Kye_Timed_Car_Poll_Rent,

GroundID,

Vehicle_GroupID,

Vehicle_Group_Short_Name,

DateID,

Time_Group,

Running_Deal_Type,

Timed_Expected_Car_Pool,

Timed_Expected_Car_Pool_Rent

Resident Timed_Car_Pool_Rent_Only;

Drop Table Timed_Car_Pool_Rent_Only;

We had an update from 2019 to 2021, and now the model doesn’t work, we don’t understand what’s wrong with it

General Question