2 Replies Latest reply: Feb 13, 2013 4:56 AM by Manideep Hv RSS

    Linking two tables (that are already linked)

      TLDR: I would like this join http://imgur.com/8zipK0v To be a left join, how to? See my script below. Directories removed for privacy.

       

      SET ThousandSep=',';

      SET DecimalSep='.';

      SET MoneyThousandSep=',';

      SET MoneyDecimalSep='.';

      SET MoneyFormat='£#,##0.00;-£#,##0.00';

      SET TimeFormat='hh:mm:ss';

      SET DateFormat='DD/MM/YYYY';

      SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

      SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

      SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

       

       

      SalesZip:

      LOAD Zip,

           Zip1,

           Zip2,

           ZipBoundary

      FROM

      a

       

       

      LEFT JOIN (SalesZip)

      Sales:

      LOAD JDEDrID,

           jdeaddressid,

           LastName,

           FirstName,

           Channel,

           AdvantageTier,

           Address1,

           Address2,

           Address3,

           Address4,

           City AS SalesCity,

           State AS StateSales,

           PostalCode AS Zip,

           Country AS SalesCountry,

           [Total Advantage Shipments 2012],

           [Advantage Shipments Per Location],

           TMName,

           RBMName,

           DirName,

           StateName AS StateNameSales,

           StateBoundary AS StateBoundarySales

      FROM

      b

       

       

      ZipTest:

      LOAD Zip,

           Zip1 AS Zip1Subs,

           Zip2 AS Zip2Subs,

           ZipBoundary AS ZipBoundarySubs

      FROM

      c

       

       

      LEFT JOIN (ZipTest)

       

       

      Subs:

      LOAD Network,

           [Account Name],

           [Ship City] AS City,

           [Ship State] AS State,

           [Ship Zip] AS Zip,

           County,

           Market,

           Country,

           StateName,

           StateBoundary,

           latitude,

           longitude

      FROM

      d

        • Re: Linking two tables (that are already linked)

          SET ThousandSep=',';

          SET DecimalSep='.';

          SET MoneyThousandSep=',';

          SET MoneyDecimalSep='.';

          SET MoneyFormat='£#,##0.00;-£#,##0.00';

          SET TimeFormat='hh:mm:ss';

          SET DateFormat='DD/MM/YYYY';

          SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

          SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

          SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

           

           

          SalesZip:

          LOAD Zip,

               Zip1,

               Zip2,

               ZipBoundary

          FROM

          a

           

           

          LEFT JOIN (SalesZip)

          Sales:

          LOAD JDEDrID,

               jdeaddressid,

               LastName,

               FirstName,

               Channel,

               AdvantageTier,

               Address1,

               Address2,

               Address3,

               Address4,

               City AS SalesCity,

               State AS StateSales,

               PostalCode AS Zip,

               Country AS SalesCountry,

               [Total Advantage Shipments 2012],

               [Advantage Shipments Per Location],

               TMName,

               RBMName,

               DirName,

               StateName AS StateNameSales,

               StateBoundary AS StateBoundarySales

          FROM

          b

           

           

          ZipTest:

          LOAD Zip,

               Zip1 AS Zip1Subs,

               Zip2 AS Zip2Subs,

               ZipBoundary AS ZipBoundarySubs

          FROM

          c

           

           

          LEFT JOIN (ZipTest)

           

           

          Subs:

          LOAD Network,

               [Account Name],

               [Ship City] AS City,

               [Ship State] AS State,

               [Ship Zip] AS Zip,

               County,

               Market,

               Country,

               StateName,

               StateBoundary,

               latitude,

               longitude

          FROM

          d

           

          left join


          load *

          resident SalesZip;

           

          drop table SalesZip;