Using @isini with other functions

omalt

Forums

Hello I've been trying to exclude results from a procurement dataset by using the following equation
 
TRANSACTION_NUMBER <> "P CARD" .AND. TRANSACTION_NUMBER <> "P-Card" .AND. TRANSACTION_NUMBER <> "CHAPS"  .AND.  .NOT. @Isini("PC",TRANSACTION_NUMBER) .AND.  .NOT. @Isini("Chaps",TRANSACTION_NUMBER)
 
It appears as though when i include the @isini parts of the equation it knocks out all the other requirements.  My assumption is use of the <> and @isini are not compatible and conflict with each other.  Or it could be the use of AND and then NOT which is conflicting the equation.  Is there another way to get around this?  Do I have to do the criteria in two steps and use the first part then extract the records before doing each of the @isini parts.  Thank you

Steven Luciani Thu, 01/31/2019 - 07:16

Hi Omalt,

This equation can be simplified by using the @nomatch and nesting in an @lower or @upper function. This assumes that "Chaps" and "PC" are not imbedded somewhere inside the transaction_number. If each string stands on its own and the case type of spelling is the only issue then this is the equation to use:

@nomatch(@lower(TRANSACTION_NUMBER),"p card","p-card","chaps","pc")

I nested @lower so I have to type my nomatching strings in lower case.

If "chaps" and "pc" are imbedded inside a transaction_number the equation would look like this:

.not. @isini("chaps",TRANSACTION_NUMBER) .and. (.not. @isini("PC",TRANSACTION_NUMBER)) .and. @nomatch(@lower(TRANSACTION_NUMBER), "chaps","p card","p-card")

In IDEA .and. .not. does not work. If you are going to try and eliminate imbedded strings in a field and have to use more than one @isini() then you need to isolate the subsequent @isini() function inside their own set of brackets.

Finally, If case sensitivity is an issue in your analysis based on your example above you would not nest the @lower in the @notmatch function and use @isin. The equation would look like this:

.not. @isin("Chaps",TRANSACTION_NUMBER) .and. (.not. @isin("pc",TRANSACTION_NUMBER)) .and. @nomatch(TRANSACTION_NUMBER, "CHAPS","P-Card",P CARD")

Cheers,

Steven

 

 

 

omalt Thu, 01/31/2019 - 08:34

Thanks Steven,  there are some embedded transaction number issues, i used your second equation which worked fine.  Thanks