4 Replies Latest reply: Oct 6, 2016 10:55 AM by Crystle Stamper RSS

    Left Join keeps adding a new record

    Crystle Stamper

      I am trying to add fields to a table that has a unique set of VINs.

       

      So first I load all the Unique VINs

      Temp:

      LOAD Distinct

          VIN

      FROM

      data\VIN_AllYears.qvd

      (qvd);


      Then I load a place holder for each year that we have data for, so each VIN has a value for each year, for comparison, in case a VIN doesn't show up in the data for one year, we can still track it.

       

      Join (Temp)

      LOAD *,

        1 as Yr_2010,

        1 as Yr_2011,

        1 as Yr_2012,

        1 as Yr_2013,

        1 as Yr_2014,

        1 as Yr_2015

      Resident Temp;

       

      Now, at this point the data is perfect, I have one record for each VIN (which is how I want to keep it). BUT when I try to add the data that I need to compare it adds an extra field for each Year field I add, giving me 6 records for each VIN, instead of just one.

       

      This is how I am adding the new counts (Standard Count: StdCnt) data currently, and it is giving me a new record for each field.


      Join (Temp)

      LOAD

        VIN,

           StdCnt_2010,

           StdCnt_2011,

           StdCnt_2012,

           StdCnt_2013,

           StdCnt_2014,

           StdCnt_2015

          

      FROM

      data\POLK_VIN_AllYears.qvd

      (qvd);


      How can I get it to join these fields and only have one record for each VIN?

       


        • Re: Left Join keeps adding a new record
          Sunny Talwar

          New record for each field? I am not sure I understand what you are trying to say here. Can you may be give an example of what you are seeing and what you intended to see?

          • Re: Left Join keeps adding a new record
            Vineeth Pujari

            Try this instead

             

             

             

            Temp:

            LOAD Distinct

                VIN,

              1 as Yr_2010,

              1 as Yr_2011,

              1 as Yr_2012,

              1 as Yr_2013,

              1 as Yr_2014,

              1 as Yr_2015

            FROM

            data\VIN_AllYears.qvd

            (qvd);

             

             

            Join (Temp)

            LOAD

              VIN,

                 StdCnt_2010,

                 StdCnt_2011,

                 StdCnt_2012,

                 StdCnt_2013,

                 StdCnt_2014,

                 StdCnt_2015

                

            FROM

            data\POLK_VIN_AllYears.qvd

            (qvd);

             

            • Re: Left Join keeps adding a new record
              Marco Wedel

              Hi,

               

              I guess the reason is because of your POLK_VIN_AllYears.qvd having 6 rows per VIN value.

              Apart from that I would suggest not to load the dimension Year hard coded in field names.

              Instead you could create an additional year field e.g using a crosstable load.

               

              hope this helps

               

              regards

               

              Marco

               

                • Re: Left Join keeps adding a new record
                  Crystle Stamper

                  Good call on the table having 6 records, that is exactly why it is happening I believe.

                   

                  I do have a field that has the years and a count for each year in another field. But I am creating an aging document and If I try to have a Year field, it will give me the 6 records for each VIN anyways. That would give me around 5 million records. If I can use the fields as the years, I can get the number down to under a million with just the unique VINS being counted. This document is going to grow yearly, adding the same million or so records every year, so I am trying to hedge against the document becoming too big.

                   

                  Thank you for your help!