Qlik Sense - Link table based on calculated field from multiple tables

[sth_wch004]

Dear Expert,

how can i Link table based on calculated field(Age) and retrieve yield(Mt/Ha)?

Attached excel and qvf file.

1) Table 1: Year of Planting

Location L1 year of planting is 2006

2) Table 2: Data

Age(Calculated field) = fiscal year - year of planting

Location: L1

From Sep- 11 till Aug-12,  age is 6

From Sep- 12 till Aug-13,  age is 7

3) table 3: benchmark

[sth_wch004 · Accepted solution]

Thanks I managed to solve it.

Left Join ([Data])

LOAD Distinct

  [Location],

  [Date],

If(Month((Date(Date(Trim(Mid(Date,Index(Date, ',')+1))))))>=$(FirstMonthOfYear), Year((Date(Date(Trim(Mid(Date,Index(Date, ',')+1))))))+1, Year((Date(Date(Trim(Mid(Date,Index(Date, ',')+1)))))))

- YEAR(Date(Date#([Year of Planting], 'YYYY'), 'YYYY'))  as [Age]

Resident [Data];  ! !