Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
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
Truly a miracle. Thank you.