topic Re: Qlik Sense - Link table based on calculated field from multiple tables in App Development

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

sth_wch004 — Tue, 10 Oct 2017 04:25:05 GMT

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

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

sth_wch004 — Tue, 10 Oct 2017 09:00:21 GMT

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]; ! !