Script to classify exist and new from 2 tables of different period

[43918084]

I want to build one more field to return1) Exist  if Global Trade Num (P0221) appear in both acctg Date (P0045) in 20200131 and 20191231

2) New if Global Trade Num (P0221) appear only in acctg Date (P0045)=20200131

2) Old if Global Trade Num (P0221) appear only in acctg Date (P0045)=20191231

But my script seems fail.  Hope I can get some expert guidance.

GLOBAL TRADE NUM (P0221)	SUM PROD CD (P0141)	Sum([FUNCTIONAL AMT(YTD)])
20200131	1096601H	500020	(1,778,256,338)
20191231	1096602H	500020	(1,919,695,278)
20200131	1096602H	500020	(1,908,026,044)
20191231	1154661H	500001	(1,946,500,000)
20200131	1154661H	500001	(1,942,325,000)
20191231	1160398H	500085	(6,368,948,000

[lorenzoconforti]

can you share your script?

[43918084]

Thank you so much.  I have been struggling for this problem for days.  This is something I have to deliver for my project and it has become the bottleneck.  I really appreciate it.

Please find below my script.  I have loaded and concatenated  the 2019 and 2020 table together as [LYCM]. Then I use the below script.  However, the result make the data duplicated and terminated is very high

Load* ,
    filename()as Period,

if("MATURITY DATE (P0288)"<date(date#('20191231','yyyyMMdd'),'yyyyMMdd'),'matured',
if("TRADE DATE (P0561)">date(date#('20191231','yyyyMMdd'),'yyyyMMdd') , 'new',
if("TRADE DATE (P0561)"<date(date#('20191231','yyyyMMdd'),'yyyyMMdd') and ("MATURITY DATE (P0288)">date(date#('20191231','yyyyMMdd'),'yyyyMMdd') or isnull("MATURITY DATE (P0288)")) and Peek('ACCTG DATE (P0045)')<>date(date#('20191231','yyyyMMdd'),'yyyyMMdd'), 'exist','terminated'))) as Status

Resident [LYCM] order by "GLOBAL TRADE NUM (P0221)", "ACCTG DATE (P0045)" desc; ;

Drop Table [LYCM];