topic Re: Conditional Left Join of Fields in QlikView

Conditional Left Join of Fields

Mon, 20 Oct 2014 04:04:55 GMT

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?

Re: Conditional Left Join of Fields

jonathandienst — Mon, 20 Oct 2014 05:16:47 GMT

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

Re: Conditional Left Join of Fields

Mon, 20 Oct 2014 06:47:22 GMT

Truly a miracle. Thank you.