IF function with multiple criteria
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
Hi,
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
It seems that I'm wrong with
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)
Hi Vito,
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.