Date comparison is not correct

[43918084]

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

[Vegar · Accepted solution]

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

[43918084]

Thank you very much Vegar for solving my problem.  I really appreciate it.

WIshing you a Merry Xmas ahead 🙂