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.
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.
