Left Join keeps adding a new record

[crystles]

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?

[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?

[vinieme12]

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);

[MarcoWedel · Accepted solution]

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

[crystles]

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!