Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Not applicable

Conditional Left Join of Fields

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?

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: Conditional Left Join of Fields

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

2 Replies
MVP
MVP

Re: Conditional Left Join of Fields

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

Not applicable

Re: Conditional Left Join of Fields

Truly a miracle. Thank you.