Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Yaakov_kris
Contributor III
Contributor III

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

 

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

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

 

error

Lines fetched: 8,250

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'Capture.PNG

 

 

 

 

 

 

 

 

 

 

Labels (3)
1 Reply
Daniele_Purrone
Support
Support

Hi @Yaakov_kris ,
we cannot find some of the references mentioned in the errors in the script. I'd recommend that you attach the full app exported without data, provided that you don't have hidden sections of the script.

Daniele - Senior Technical Support Engineer & SaaS Support Coordinator at Qlik
If a post helps to resolve your issue, please accept it as a Solution.