Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have below script that try to determine whether a Trade num is exist, matured, new.
The logic is :
(1) if the trade date is > accounting date , it is new
(2) if the maturity date is < 1st Jan 2020 (year start of accounting date), it is mature
(3) if the maturity is > accounting date, it is exist.
(4) If there is no date reference, treat the deal is exist.
Script is as below:
if(len(trim("TRADE DATE (P0561)"))=0 and len(trim("MATURITY DATE (P0288)"))=0,'exist',
if(Date(date#("TRADE DATE (P0561)",'yyyyMMdd'),'YYYYMMDD')>=(yearSTART(Date(Date#("ACCTG DATE (P0045)",'yyyyMMdd'),'YYYYMMDD'))
and Date(Date#("MATURITY DATE (P0288)",'yyyyMMdd'),'YYYYMMDD')> Date(Date#("ACCTG DATE (P0045)",'yyyyMMdd'),'YYYYMMDD')),'new',
if(Date(Date#("MATURITY DATE (P0288)",'yyyyMMdd'),'YYYYMMDD')<(yearSTART(Date(Date#("ACCTG DATE (P0045)",'yyyyMMdd'),'YYYYMMDD')) ),'matured','exist'))) as status,
I don't know why the result for below is new. I expect the correct type is exist .
Hope I can get some guidance. Many thanks.
GLOBAL TRADE NUM (P0221) | TRADE DATE (P0561) | MATURITY DATE (P0288) | ACCTG DATE (P0045) |
1000328H | 20120419 | 20200423 | 20201130 |
1000328H | 20120419 | 20200423 | 20201130 |
1001853H | 20120423 | 20200425 | 20201130 |
1001853H | 20120423 | 20200425 | 20201130 |
1004253H | 20120427 | 20190501 | 20201130 |
1004291H | 20120427 | 20191113 | 20201130 |
I think you have some issues with the parenthesis grouping the logical statements. I removed a parenthesis from your expression and it should now work as intended. (I also removed some Date() formatting as they are not necessary in your expression.
if( len(trim("TRADE DATE (P0561)"))=0 and len(trim("MATURITY DATE (P0288)"))=0,'exist',
if( date#("TRADE DATE (P0561)",'yyyyMMdd')>=yearSTART(Date#("ACCTG DATE (P0045)",'yyyyMMdd')) and Date#("MATURITY DATE (P0288)",'yyyyMMdd')> Date#("ACCTG DATE (P0045)",'yyyyMMdd'),'new',
if( Date#("MATURITY DATE (P0288)",'yyyyMMdd')<(yearSTART(Date#("ACCTG DATE (P0045)",'yyyyMMdd')) ),'matured','exist'))) as status
This expression return a mix of 'matured' and 'exist'.
I think you have some issues with the parenthesis grouping the logical statements. I removed a parenthesis from your expression and it should now work as intended. (I also removed some Date() formatting as they are not necessary in your expression.
if( len(trim("TRADE DATE (P0561)"))=0 and len(trim("MATURITY DATE (P0288)"))=0,'exist',
if( date#("TRADE DATE (P0561)",'yyyyMMdd')>=yearSTART(Date#("ACCTG DATE (P0045)",'yyyyMMdd')) and Date#("MATURITY DATE (P0288)",'yyyyMMdd')> Date#("ACCTG DATE (P0045)",'yyyyMMdd'),'new',
if( Date#("MATURITY DATE (P0288)",'yyyyMMdd')<(yearSTART(Date#("ACCTG DATE (P0045)",'yyyyMMdd')) ),'matured','exist'))) as status
This expression return a mix of 'matured' and 'exist'.
Thank you very much Vegar for solving my problem. I really appreciate it.
WIshing you a Merry Xmas ahead 🙂