3 Replies Latest reply: Feb 14, 2013 10:51 AM by Eduardo Correa da Silva RSS

    Problem with join

      I have this weird problem with a join:
      Contacts:
      ContactNoNameCustomerNoVendorNo
      CT001

      Name A

      CUS0022
      CT002Name BVEN0025
      CT003Name CCUS0024
      To join with table Entries:
      Entry NoItem NoSource TypeSource No
      1I123VendorVEN0025
      2I456CustomerCUS0022
      So I want to add the customer or vendor name to the entries. When I use this script it doesn't work, it just adds the contacts below the entries.
      Contacts:
      LOAD
          "No_" as ContactNo,
          Name,
         "Customer No_" as CustomerNo,
          "Vendor No_" as VendorNo;

      SQL SELECT
          "No_",
          Name,
          "Customer No_",
          "Vendor No_"
      FROM $(@DatabaseName).dbo."$(@CompanyName)$Contact";
      Entries:
      LOAD 
      "Entry No_",
      "Item No_",
      "Posting Date",
      "Document No_",
        if("Source Type" = '1', "Source No_") as CustomerNo,  
        if("Source Type" = '2', "Source No_") as VendorNo;
         //The problem has something to do with this, if I remove the if-construction and only load the customers, the join works perfectly
      SQL SELECT
      "Entry No_",
      "Item No_",
      "Posting Date",
      "Document No_",
      "Source Type",
      "Source No_"
      FROM $(@DatabaseName)
      .dbo."$(@CompanyName)$Value Entry";


      NewTable:
      NoConcatenate load * Resident Entries;
      join (NewTable) LOAD * Resident Contacts;
      Drop table Entries;
      Drop table Contacts;
        • Re: Problem with join
          Gysbert Wassenaar

          Try loading your contacts in two batches to fill a field "Source No" so you can associate your tables properly.

           

          Contacts:

          LOAD
              "No_" as ContactNo,
              Name,
             "Customer No_" as CustomerNo,
              "Vendor No_" as VendorNo,

          "Customer No_" as "Source No";

          SQL SELECT
              "No_",
              Name,
              "Customer No_",
              "Vendor No_"
          FROM $(@DatabaseName).dbo."$(@CompanyName)$Contact"

          WHERE "Customer No_" IS NOT NULL;

           

          LOAD
              "No_" as ContactNo,
              Name,
             "Customer No_" as CustomerNo,
              "Vendor No_" as VendorNo,

          "Vendor No_" as "Source No";

          SQL SELECT
              "No_",
              Name,
              "Customer No_",
              "Vendor No_"
          FROM $(@DatabaseName).dbo."$(@CompanyName)$Contact"

          WHERE "Vendor No_" IS NOT NULL;

           

          SQL SELECT
              "No_",
              Name,
              "Customer No_",
              "Vendor No_"
          FROM $(@DatabaseName).dbo."$(@CompanyName)$Contact";

          Entries:

          LOAD 
          "Entry No_",
          "Item No_",
          "Posting Date",
          "Document No_",

          "Source No_" as "Source No"

          SQL SELECT
          "Entry No_",
          "Item No_",
          "Posting Date",
          "Document No_",
          "Source Type",
          "Source No_"
          FROM $(@DatabaseName)
          .dbo."$(@CompanyName)$Value Entry";

            • Re: Problem with join

              Ty for your help. I forgot to mention that it's possible that one contact has CustomerNo aswell as VendorNo filled in. Your method doesn't give good results in that situation.

                • Re: Problem with join

                  Hi,

                   

                  Change your IF statement

                   

                  IF( "Source Type" = '1', "Source No_") as CustomerNo,

                  IF( "Source Type" = '2', "Source No_") as VendorNo;

                   

                  TO

                   

                  IF( "Entry No_" = '1', "Source No_") as CustomerNo,

                  IF( "Entry No_" = '2', "Source No_") as VendorNo;

                   

                  "Source Type" are "Vendor" or "Custumer".

                   

                  NOT "1" or "2"

                   

                  You need to have two Table Entries:

                   

                  One for "Vendor" and other for "Custumer"

                   

                  Make a join between Contacts and Entries_Vendor

                   

                  After between result join with Entries_Custumer.

                   

                   

                  Ex.:

                   

                  NewTable:

                  NoConcatenate

                  LOAD

                       ContactNo,

                       Name,

                       CustomerNo,

                       VendorNo

                  Resident Contacts;

                   

                  JOIN (NewTable)

                   

                  LOAD

                       "Entry No_",

                       "Item No_",

                       "Posting Date",

                       "Document No_",

                       CustomerNo

                  //     VendorNo

                  Resident Entries

                  WHERE Source Type = '1';

                   

                  JOIN (NewTable)

                   

                  LOAD

                       "Entry No_",

                       "Item No_",

                       "Posting Date",

                       "Document No_",

                  //     CustomerNo

                       VendorNo

                  Resident Entries

                  WHERE Source Type = '2';

                   

                   

                   

                   

                   

                   

                  Drop table Entries;

                  Drop table Contacts;