Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

sth_wch004
New Contributor III

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

Dear Expert,

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

Attached excel and qvf file.

Capture.PNG

1) Table 1: Year of Planting

Location L1 year of planting is 2006

Capture.PNG

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

Capture.PNG

3) table 3: benchmark

Capture.PNG

1 Solution

Accepted Solutions
sth_wch004
New Contributor III

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

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

1 Reply
sth_wch004
New Contributor III

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

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

Community Browser