Urgent help

[Apeksha]

I have two tables innovation table and d_material table and i want the 3rd table that is output table.

I have 3 fields in innovation table innovation_desc, innovation yr, ind_brand_code. And  there is another table d_material and which has only indi_brand_code. And innovation table doesn't contain all the ind_brand_code  present in d_material and hence innovation yr of those brands are also not known. Now what i want to build a 3rd tabke that is output table that contain all ind_brand_code of both d_material and innovation table and also Innovation_desc and innovation yr but brand code in which innovation yr- FY difference is more than 5yrs fall in non- innovation and also brands whose yr of innovation is not known should fall in non- innovation category.

[stevejoyce]

My first thought is outer join.  But easier for this is to use where not exists.  I'm a little confused on what you explained but this should do it.  And i hard coded max year = 2021.  you can do year(today()) or get the max year from innovation table, whatever you need to do to figure out what your max year is.

let vMaxYear = 2021;

//Load innovation table

output_table:

load *

from <innovation_table_from_statement>
where Innovation_Year > $(vMaxYear) - 5

;

concatenate(output_table)

load

Ind_brand_code

,'non-innovation' as [Innovation_desc]

,'non-innovation' as [Innovation_Year]

from from <material_table_from_statement>

where not exists (Ind_brand_code)

;

that should do it.  please reply if it does not or mark as correct answer.