IF function with multiple criteria

VITOPIEPOLI

Forums

Good afternoon,
I am trying to append a new field that return a value for each of the transaction recorded based on specific dates it is performed.
I am pretty new with the IDEA Script and the logic behind it. I come up with the following code;
@if((@match( TRANS_DATE, @ctod(""01/01/2018"",""DD/MM/YYYY"")) .OR. @match( TRANS_DATE, @ctod(""02/01/2018"",""DD/MM/YYYY"")) .OR. @match( TRANS_DATE, @ctod(""17/03/2018"",""DD/MM/YYYY"")) .OR. @match( TRANS_DATE, @ctod(""19/03/2018"",""DD/MM/YYYY"")) .OR. @match( TRANS_DATE, @ctod(""30/03/2018"",""DD/MM/YYYY"")) .OR. @match( TRANS_DATE, @ctod(""02/04/2018"",""DD/MM/YYYY"")) .OR. @match( TRANS_DATE, @ctod(""07/05/2018"",""DD/MM/YYYY"")) .OR. @match( TRANS_DATE, @ctod(""28/05/2018"",""DD/MM/YYYY"")) .OR. @match( TRANS_DATE, @ctod(""12/07/2018"",""DD/MM/YYYY"")) .OR. @match( TRANS_DATE, @ctod(""06/08/2018"",""DD/MM/YYYY"")) .OR. @match( TRANS_DATE, @ctod(""27/08/2018"",""DD/MM/YYYY"")) .OR. @match( TRANS_DATE, @ctod(""30/11/2018"",""DD/MM/YYYY"")) .OR. @match( TRANS_DATE, @ctod(""25/12/2018"",""DD/MM/YYYY"")) .OR. @match( TRANS_DATE, @ctod(""26/12/2018"",""DD/MM/YYYY"")) ==1, "5", "1")
I supposed that the mathcing element as 1 with TRUE. Then if the transcation is posted during one dates I assign a char value 5 otherwise it would assign 1.
I was able to run the extraction of the fields as another database but I need too give to each transaction a risk score for testing purposes.
 
Thank you very much for your support.
 
Vito
 

klmi Wed, 10/30/2019 - 03:34

Hi Vito,
I hope I can help you with the following annotations:
1) What you posted has noting to do with IDEAScript. Your "code" is a normal IDEA function / criteria however it may look advanced for beginners.
2) When I read your title "IF function with multiple criteria" first it came to my mind that you are maybe looking for the @compif function. Here you can check different cases with one formula instead some ifs.

VITOPIEPOLI Wed, 10/30/2019 - 08:20

Hi,
I have tried with a @compif fuction but I don't get the result wanted:
@CompIf(@match(@Dtoc(TRANS_DATE, "DD-MM-YYYY"), "03/10/2017"),5,@match( @Dtoc(TRANS_DATE, "DD-MM-YYYY"), "01/11/2017"), 5,1,1)
I am appending a new field at the end of the spreadsheet giving to each transaction a rate (1 or 5).
This time the function is not returning any error but does not assign the right value, that is the field is filled with value 1 only.
Any suggestion?
Many thanks
 
 

klmi Wed, 10/30/2019 - 10:57

It seems that I'm wrong with my interpretation of what you want to do. Due to the fact that you want to give each transaction a risk score I assumed that you have more cases than only 1 and 5. If not I see no advantage in using @compif compared to @if.
 
Besides I don't understand why you compare the dates with @match but have only one element in the list. To compare dates you can easily use:
@if(TRANS_DATE="20171003"; 5; 1)
 
Likely the formula in your last post doesn't have the output you expect caused by a formating issue.
@CompIf(@match(@Dtoc(TRANS_DATE, "DD/MM/YYYY"), "03/10/2017"),5,@match( @Dtoc(TRANS_DATE, "DD/MM/YYYY"), "01/11/2017"), 5,1,1)
 
or
 
@CompIf(@match(@Dtoc(TRANS_DATE, "DD-MM-YYYY"), "03-10-2017"),5,@match( @Dtoc(TRANS_DATE, "DD-MM-YYYY"), "01-11-2017"), 5,1,1)