3 Replies Latest reply: Feb 8, 2017 3:06 AM by Stefan Wühl RSS

    Using Left Join in one to many relation

    Magnus Rydberg

      I have two files A and B that has a "one to many relation" and I'm using a left join.

      I've got duplicate rows since B can contain several records for each A. B can also miss corresponding records for some A records thats why I want to do a Left join.

      How should I write in the script to only join with the first record in B if there are more than one?


      Code example today:

      // Transactions

      LOAD InvoiceType,

          Status as InvoiceStatus,

          Network as InvoiceNetwork,

          TransportCompany as InvoiceCompany,

          TransportFacility as InvoiceFacility,

          CustomerCompany as InvoiceCustomerCompany,

          CustomerFacility as InvoiceCustomerFacility,

          CustomerAlias as InvoiceCustomerAlias,

          LONumber as InvoiceOrderNumber,

          OrderType as InvoiceOrderType;

      SQL SELECT * FROM "MyL_PROD_220".OTSDATA.InvoiceTransaction where Network='NET1';

       

      // Comments

      Left Join

      LOAD  StringKey1 as InvoiceOrderNumber,

             CommentString as InvoiceComment;

      SQL SELECT * FROM "MyL_PROD_220".OTSDATA.Comment ;