Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
can you share your script?
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];