topic Re: Script to classify exist and new from 2 tables of different period in App Development

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

43918084 — Mon, 20 Dec 2021 21:32:42 GMT

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

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

lorenzoconforti — Thu, 02 Apr 2020 16:25:44 GMT

can you share your script?

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

43918084 — Thu, 02 Apr 2020 21:31:32 GMT

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