7 Replies Latest reply: Jan 19, 2016 1:40 PM by jhonatan ben ami RSS

    Key table that isn't loading

    jhonatan ben ami

      hello everyone

      i am writing the following script in order to create an key table

      for some reason the key table doesn't appear to be loading and i don't see at the data model viewer although i am not getting ant errors...

       

      here is the script:

      KEY_TABLE_TEMP:

      LOAD

      PART_ID

      RESIDENT

      PARTS;

       

       

      CONCATENATE

      LOAD CUST_CUST

      RESIDENT

      CUSTOMERS;

       

       

      CONCATENATE

      LOAD

      ORD_ORDID

      RESIDENT

      ORDERS;

       

       

      CONCATENATE

      LOAD

      OPO_OPORDERID

      RESIDENT

      OPEN_ORDERS;

       

       

      CONCATENATE

      LOAD

      INV_ID

      RESIDENT

      INVOICES;

       

       

      KEY_TABLE:

      LOAD

      PART_ID,

      CUST_CUST,

      ORD_ORDID,

      OPO_OPORDERID,

      INV_ID

      RESIDENT KEY_TABLE_TEMP;

       

       

      DROP TABLE KEY_TABLE_TEMP;

        • Re: Key table that isn't loading
          Massimo Grossi

          try

           

          KEY_TABLE:

          noconcatenate

          LOAD

          PART_ID,

          CUST_CUST,

          ORD_ORDID,

          OPO_OPORDERID,

          INV_ID

          RESIDENT KEY_TABLE_TEMP;

          • Re: Key table that isn't loading
            Sunny Talwar

            Try this:

             

            KEY_TABLE:

            NoConcatenate

            LOAD

            PART_ID,

            CUST_CUST,

            ORD_ORDID,

            OPO_OPORDERID,

            INV_ID

            RESIDENT KEY_TABLE_TEMP;

            • Re: Key table that isn't loading
              Sangram Reddy

              Hi Jhonatan,

               

              When two tables in the load script have the same field names , they get concatenated automatically. So as stalwar1 mentioned, you need to explicitly use the NoConcatenate key word before the load of the second table.

               

              Thnaks and Regards,

              Sangram Reddy.

              • Re: Key table that isn't loading
                Jonathan Dienst

                If you are expecting to have association between the various IDs in the link table, then you will be disappointed with the approach above (including the proposed fixes). Simply concatenating the ID fields will not create any associations as the other ID fields will all be null. For example, when you load INV_ID, all the other ID fields are filled with null values for the rows containing INV_ID. Likewise, the rows containing PART_ID will have null values in the other fields. So if you select an invoice value (linked via INV_ID), you will exclude all values of all the other links.

                 

                Presumably, your data sources for invoices and orders contain references to customer, part numbers, etc. You will need to create the link table by joining these on the appropriate fields or bringing in the associations. Something like:

                 

                     LOAD DISTINCT ORDERID,

                          CUST_CUST,

                          PARTID

                     RESIDENT ORDERS;

                 

                     CONCATENATE

                     LOAD DISTINCT OPO_OPORDERID,

                          CUST_CUST,

                          PARTID

                     RESIDENT OPEN_ORDERS;


                     CONCATENATE

                     LOAD DISTINCT INV_ID,

                          CUST_CUST,

                          PARTID

                     RESIDENT INVOICES;


                     JOIN LOAD DISTINCT   // add parts not already loaded

                          PART_ID

                     RESIDENT PARTS;

                 

                     JOIN LOAD DISTINCT  // add customers not already loaded

                          CUST_CUST

                     RESIDENT CUSTOMERS;

                 

                If that is not right, I suggest that you shat more details about your data sctructure and what associations you need. For example, are invoices linked to orders?