Conditional Left Join of Fields

[#1]

Hi,

I have a fact table like so:

customer:

load

%Key1,

Year,

from ...

now I want to load and join a lookup table. This lookup has the following fields:

%key, year2011value, year2012value, year2013value

When joining, I need to pick one of the yearXXXXvalue from each row in the lookup that corresponds to the Year column of the joining row in the customer table. So I tried the following join:

left join (customer)

LOAD

  %Key1,

   pick(Year-2010,year2011value, year2012value, year2013value) as myField

FROM lookup.csv

But QV complains "file not found Year" . Apparently, It can not reference the Year column of the customer table while joining. How can this be done?

[jonathandienst · Accepted solution]

Hi

I suggest that you do a cross-table load of the lookup table into a temporary table and then join that to your fact table. Something like:

customer:

LOAD ....

TempLookup:

Crosstable (Year, Value)

LOAD %key,

     year2011value,

     year2012value,

     year2013value

FROM lookup.csv;

Left Join (customer)

LOAD %key,

     Num(Mid(Year, 5, 4)) As Year,     // get year value out of the label

     Value As myField

Resident TempLookup;

DROP Table TempLookup;

HTH

Jonathan

[#3]

Truly a miracle. Thank you.