5 Replies Latest reply: Jan 4, 2017 3:40 AM by Vineeth Pujari RSS

    Reading two records having a single index

    Olivier DAHER

      Hello everyone,

      The concatenation of a file A and a file B gives the following:

       

      Customer_Code     Customer_Name

      123                         Client_ABC

      123                         Client_EFG

       

      I wish to keep the first one: (Client_ABC)

      How to do ?

      Thank you

        • Re: Reading two records having a single index
          Vineeth Pujari

          So you only want to load records from File B that do not exist in File A ?

           

          Try as below

           

          Load

          Customer_Code,

          Customer_Name

          From FileA;

           

          concatenate

           

          Load

          Customer_Code,

          Customer_Name

          From FileB

          WHERE NOT EXISTS(Customer_Code);

          • Re: Reading two records having a single index
            Olivier DAHER

            Thank you very much for your help, however you must have identical field names in both tables.

            • Re: Reading two records having a single index
              Olivier DAHER

              // The 1st File

              [Clients_S]:

              LOAD

                [Code Client] as Code_Client,

                Client

              FROM [D:\N\QVD\Clients_S.qvd]

              (qvd);

              Store Clients_S into [D:\N\TMP\Clients_Sage.qvd];

              Drop table Clients_S;

               

              // The 2nd File

              Clients_Sellsy:

              LOAD

                  [Code compte auxiliaire] as Code_Client,

                [Client]

              FROM [D:\N\Sellsy\Règlements.csv]

              (txt, codepage is 1252, embedded labels, delimiter is ';', msq);

              Store Clients_Sellsy into [D:\N\TMP\Clients_Sellsy.qvd];

              Drop table Clients_Sellsy;

               

               

              Clients:

              Load *

              From [D:\N\TMP\Clients_Sage.qvd]

              (qvd);

               

              Load *

              From [D:\N\TMP\Clients_Sellsy.qvd]

              (qvd)

              WHERE NOT EXISTS ([Code_Client]);

              Store Clients into [D:\N\QVD\Clients.qvd];

                • Re: Reading two records having a single index
                  Vineeth Pujari

                  Not Necessarily

                  For your Script

                   

                  // The 1st File
                  [Clients_S]:
                  LOAD
                  [Code Client] as Code_Client,
                  Client
                  FROM [D:\N\QVD\Clients_S.qvd] (qvd);

                   
                  // The 2nd File
                  Clients_Sellsy:
                   
                  LOAD
                   
                  [Code compte auxiliaire] as Code_Client,
                   
                  [Client]
                   
                  FROM [D:\N\Sellsy\Règlements.csv]
                  (
                  txt, codepage is 1252, embedded labels, delimiter is ';', msq)
                  Where Not Exists (Code_Client, [Code compte auxiliaire]);

                   

                   

                   

                   

                   

                  try below example

                   

                  Load * inline [
                  Customer_Code,Customer_Name
                  123,ABC
                  546,XYZ
                  ]
                  ;
                   
                  concatenate
                   
                  Load CCode as Customer_Code,Customer_Name inline [
                  CCode,Customer_Name
                  123,XYZ 
                  789,CVB
                  ]

                  WHERE NOT EXISTS(Customer_Code,CCode);