New Macros - IsWhitespace, IsNumeric, Stuff, IsDate and IndexOf
Written By Jennifer Gatling
UPDATE: Last modified on 10/5/2017 12:17:19 PM
New Macro commands have been added to our pasUNITY Enterprise Suite macro-enabled products to make you more productive than ever. You are most likely to use these macros in the pasTransfer ETL or pasUnity Automation products.
ISNUMERICThe ISNUMERIC macro determines if a numeric value or currency amount can be detected and parsed from a character string. This would be good to use in instances where you are building a pasTransfer configuration using the Excel file source and lets say you have some lines with numeric account codes and some lines with account names in an Account column. In this particular case you only want to pull in the numeric accounts. You could use this macro to determine if the lines contain a numeric account and if it doesn’t to DNT the line. Lets say the Account code ([Copy|Account]) in this example is ‘4576’.
Result and Explanation: This will return 4576. The macro first checks if the [Copy|Account] macro is numeric which it is since it is 4576 and this returns true. Since the macro returned true, then it is going to just return the Account code. Had the [Copy|Account] been a non numeric value such as ‘ABYX’, the macro would have returned false and resulted in a DNT value.
ISWHITESPACEThe ISWHITESPACE macro determines if a character string contains only whitespace characters or is empty. This macro would be good to use in instances where you are dealing with a file that puts blanks in the transaction amount fields instead of using zeros. You can use this macro to determine if the field is blank and then put a zero value in the field instead using an IF/THEN macro. Lets say the Transaction Amount ([Copy|TransactionAmount]) in this example is blank.
Result and Explanation: This will return 0. The macro first checks if the [Copy|TransactionAmount] macro is blank which it is and this returns true. Since the macro returned true, then it is going to return the value 0. Had the [Copy|TransactionAmount] field been a value such as 25, the macro would have returned false and then resulted in the Transaction Amount value.
INDEXOFThe IndexOf macro returns the 0-based index of a character string within another character string. If no match is found the IndexOf macro will return -1. An example of how this can be used is lets say you have a bank transaction integration and you want to determine from the Transaction Reference field whether it is a Chase bank transaction because you only want to process Chase transactions and DNT all other bank transactions. The macro below will search the transaction reference for the word ‘Chase’ and return a -1 if it cannot find it. If the macro returns a -1, then it will DNT the line. Lets say the Transaction Reference ([Copy|TransactionReference]) in this example is ‘Bank Transaction Chase’ and the Account code is ‘9837’.
Result and Explanation: This will return 9837. The macro first checks if the [Copy|TransactionReference] field contains the word ‘Chase’ and if so, returns the index position it is in which would be 17. Since this value isn’t -1, then it will return the [Copy|AccountCode] value which is 9837. Had the Transaction Reference been ‘Bank Transaction Discover’, the macro would return the value -1 and therefor result in a DNT value.
STUFFThe Stuff macro inserts a string of characters into an existing string of characters and optionally replaces an existing string of characters. An example of a situation where this could be used is if you have an account string that is composed of codes such as an account code, segment, and department like the following: 1234-P200-101 and you wish to exchange the segment code with a generic code you could do the following:
Result and Explanation: This will return 1234-ZZZZ-101. The 5th character in the string is where P starts and 4 characters following that would be ‘P200’. The string that will be replacing P200 is ZZZZ.
ISDATEThe IsDate macro determines if a date or time value can be detected and parsed from a character string. An example of how this macro could be used is lets say you have a report that gets uploaded to a dropbox and it kicks off a pasUnity job. The name of the report is the date such as ‘2017-08-12.xls’ and we want to parse this date to use as the Transaction Date in the job. If the client forgets to supply date in the filename then we just want to use the DataFileDate (date the report was dropped on the portal) as the Transaction date. Lets say a user uploads a file and the name of the file is ‘MayMicrosFile.xls’ and it was uploaded to the portal on calendar date 2017-10-01. The following macro is in place for Transaction Date:
Result and Explanation: This will return a Transaction Date of 2017-10-01. Due to the filename not being a date, the IsDate macro will return ‘false’ and therefor use the date the file was dropped on the portal as the transaction date. Had the name of the report been 2017-08-12.xls then the Transaction Date would have been 2017-08-12.
These and many more macro commands are fully documented with samples in documentation and help files for the latest versions of all pasUNITY Enterprise Suite products and online @ https://www.pasportal.com/Documentation
TagsBirchStreetChoiceExcelFintechFTPFTPSMacrosMappingODBCOLEDBOnQOperapasTransferpasUnityPMSSFTPSQL ServerSQLServerUniversal ConnectionsValidationXML