7 Replies Latest reply: Jun 29, 2014 2:03 AM by Grant Ryan RSS

    Use of Resident in Load Script   Table Not Found  why?

      On one tab, I load (successfully) the following table, shortened for brevity.  Like I say, there is no issue loading the SO table.

      SO:
      LOAD
      SO_NUMBER as %SOKey,
      LINE as SO.LINE,
      LOT as SO.LOT,
      SUB_LOT as SO.SUB_LOT;
      SQL SELECT
      soh.SO_NUMBER,
      soh.LINE,
      soh.LOT,
      soh.SUB_LOT

      FROM SO_SOH soh
      LEFT OUTER JOIN SODetail_SOD sod on soh.SO_NUMBER=sod.SO_NUMBER;

      //the following gives the "Table Not Found" error.  I have this code on the next adjacent  tab...
      SO_Inv:
      REPLACE LOAD
      LINE as SO_Inv.LINE,
      LOT as SO_Inv.LOT,
      SUB_LOT as SO_Inv.SUB_LOT
      RESIDENT SO;
      // I am simply trying to create a table called  SO_Inv from columns on the original SO table.    ANY IDEAS?  thanks.
        • Re: Use of Resident in Load Script   Table Not Found  why?
          Jonathan Dienst

          Hi

           

          But is the SO table loading any data? The table will not be created if the SQL query returns no data.

           

          If the answer is Yes, does the script load another table with the same fields as SO before this? This would result in the data being concatenated to the other table and SO would not be created. In this case, you put a NoConcatenate statement

           

          SO:

          NoConcatenate

          LOAD ...

           

          Do you need the Replace?

           

          Hope that helps

          Jonathan

            • Re: Use of Resident in Load Script   Table Not Found  why?

              (How come when I click reply on Jonathans' post, it says I'm replying to Phani?)  Anyway..Thanks for the responses.

              Yes,  SO has, and continues to load 2 million plus records.  And No, there are no tables that load before or after these two tabs (containing SO and SO_Inv)  that read from either SO or its source, soh.

               

              I added the NOCONCATENATE and get the same error.  The REPLACE was just there as a testing option.

              I took REPLACE out and still get the error.   Not working, "table not found".   Very odd.

               

              Here is the absolute full SO script,  SO_Inv remains as above.   I appreciate any direction. :>>>>>

               

               

              // ===================================================================================================
              // SO (Joned SOH and SOD)
              // ===================================================================================================

               

               


              SO:

              NoConcatenate
              LOAD

              SO_NUMBER as %SOKey,
              AutoNumberHash128(LOT & SUB_LOT) as %POSOKey,
              AutoNumberHash128(LOT & SUB_LOT) as %SOCSKey,
              TRIP_NUMBER as %TripKey,

              PRODUCT_CODE as SO.ProductKey, // Product Key
              SHIP_TO as %CustomerKey, // Customer Key
              CUSTOMER_CODE as %OwnerKey, // Owner Key - corrsponds to the vendor code
              WAREHOUSE,
              date(date#(EXPECTED_DELIVERY_DATE, 'YYYYMMDD'), 'MM/DD/YYYY') as "Expected Delivery Date",
              ORIGINATION_CODE,
              CUSTOMER_PO_NUMBER,
              STATUS_O_S_T_V,
              BILL_DATE,
              CUSTOMER_PO_2ND_REFERENCE,
              SODID,
              LINE as SO.LINE,
              SHIPPED_QUANTITY as SO.SHIPPED_QUANTITY,
              WEIGHT,
              LOT as SO.LOT,
              SUB_LOT as SO.SUB_LOT,
              UNIT_PRICE,
              ORDER_QUANTITY as SO.ORDER_QUANTITY,
              STATUS_O_C_T_V
              ;
              SQL SELECT
              soh.SOHID,
              soh.SO_NUMBER,
              soh.TRIP_NUMBER,
              soh.SHIP_TO,
              soh.SHIP_TO_STATE,
              soh.SHIP_TO_ZIP,
              soh.WAREHOUSE,
              soh.SHIP_TO_CITY,
              soh.CUSTOMER_CODE,
              soh.EXPECTED_DELIVERY_DATE,
              soh.ORIGINATION_CODE,
              soh.CUSTOMER_PO_NUMBER,
              soh.STATUS_O_S_T_V,
              soh.BILL_DATE,
              soh.CUSTOMER_PO_2ND_REFERENCE,
              sod.SODID,
              sod.PRODUCT_CODE,
              sod.LINE,
              sod.SHIPPED_QUANTITY,
              sod.WEIGHT,
              sod.LOT,
              sod.SUB_LOT,
              sod.UNIT_PRICE,
              sod.ORDER_QUANTITY,
              sod.STATUS_O_C_T_V

              FROM SO_SOH soh
              LEFT OUTER JOIN SODetail_SOD sod on soh.SO_NUMBER=sod.SO_NUMBER
              //WHERE EXPECTED_DELIVERY_DATE >= '$(StartDate)'                                        // ??
              ;




              // -----------
              //SO2TripMap:
              //MAPPING LOAD DISTINCT
              //     %SOKey as A,
              //     %TripKey as B
              //RESIDENT SO;

                • Re: Use of Resident in Load Script   Table Not Found  why?
                  John Anderson

                  If you have 2mill records in SO, you should be able to perform a resident load.

                   

                  I wonder if its not a syntax issue (with Qlikview).  For example, I find that inline tables can't have a number at the start of the field, but its ok if you enclose them in square brackets.

                   

                  I wonder if you could look at removing the special characters in your SO table for starters.  or encapsulate them in square brackets.  [%SOKey] AS A

                  • Re: Use of Resident in Load Script   Table Not Found  why?
                    Jonathan Dienst

                    Hi

                     

                    Perhaps QV does not like the table name SO - I suggest that you enclose it with square brackets [SO].

                     

                    Jonathan

                      • Re: Use of Resident in Load Script   Table Not Found  why?

                        Thanks again for all your help.  I tried brackets, no good.

                        I have whittled the script down.  [SO] still loads records as before. Same error on SO_Inv however.

                         

                        SO_Inv tab now has:

                         

                         

                        SO_Inv:

                        LOAD
                        SO_NUMBER
                        //     LINE as SO_Inv.LINE
                        //LOT as SO_Inv.LOT,
                        //     SUB_LOT as SO_Inv.SUB_LOT
                        RESIDENT [S0];

                        -----------------------------------------------

                         

                        SO tab now has:

                         

                        [SO]:

                        LOAD
                        SO_NUMBER ;

                        SQL

                        SELECT
                        soh.SOHID,
                        soh.SO_NUMBER,
                        soh.TRIP_NUMBER,
                        soh.SHIP_TO,
                        soh.SHIP_TO_STATE,
                        soh.SHIP_TO_ZIP,
                        soh.WAREHOUSE,
                        soh.SHIP_TO_CITY,
                        soh.CUSTOMER_CODE,
                        soh.EXPECTED_DELIVERY_DATE,
                        soh.ORIGINATION_CODE,
                        soh.CUSTOMER_PO_NUMBER,
                        soh.STATUS_O_S_T_V,
                        soh.BILL_DATE,
                        soh.CUSTOMER_PO_2ND_REFERENCE,
                        sod.SODID,
                        sod.PRODUCT_CODE,
                        sod.LINE,
                        sod.SHIPPED_QUANTITY,
                        sod.WEIGHT,
                        sod.LOT,
                        sod.SUB_LOT,
                        sod.UNIT_PRICE,
                        sod.ORDER_QUANTITY,
                        sod.STATUS_O_C_T_V      
                        FROM SO_SOH soh
                        LEFT OUTER JOIN SODetail_SOD sod on soh.SO_NUMBER=sod.SO_NUMBER;

                         

                         

                        -------------------------------

                        Error:

                         

                        Table not found
                        SO_Inv:
                        LOAD
                        SO_NUMBER

                        RESIDENT [S0]

                        ------------------ Next step would be to offload what I can from QV to sql server, from where the source data comes!

                  • Re: Use of Resident in Load Script   Table Not Found  why?
                    Phaneendra Kunche

                    Hi,

                     

                    you can use either jonathan's "NoConcatenate".

                     

                    or you can use "REPLACE only".

                     

                     

                    Regards,

                    Phani