topic Re: Left Join keeps adding a new record in QlikView

Left Join keeps adding a new record

crystles — Wed, 05 Oct 2016 14:38:17 GMT

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 — Wed, 05 Oct 2016 14:43:40 GMT

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

vinieme12 — Wed, 05 Oct 2016 15:29:07 GMT

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

MarcoWedel — Wed, 05 Oct 2016 23:25:13 GMT

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

crystles — Thu, 06 Oct 2016 14:55:16 GMT

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!