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
omalt
Offline
Last seen: 4 years 11 months ago
Joined: 08/15/2018 - 08:37
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
Thanks Steven, there are some embedded transaction number issues, i used your second equation which worked fine. Thanks