Custom Functions הן הרחבה של הפונקציות הבנויות, אליהן נגיע בעת יצירת פילטרים על הנתונים או בעת הוספת שדות חדשים לבסיס נתונים.

החבילה זמינה להורידה דרך אתר התמיכה של חברת caseware - idea. האתר נפתח בלחיצה על צלמית המטוס, כחלון בתוך idea. נאתר בלשונית IDEA LAB  את Custom Functions. בלחיצה על download יורד קובץ zip למחשב המשתמש.

בפתיחת קובץ ה zip מתקבלים שני קבצים:

  • Custom Functions Document בפורמט PDF
  • Custom Functions.exe

להתקנת הפונקציות במחשב המשתמש נקליק על קובץ ה exe . בסיום ההתקנה הפונקציות זמינות לשימוש:

לכל פונקציה כמו בפונקציות הבנויות שהותקנו בעת התקנת התוכנה, מסך Help המציג מה מהות הפונקציה, אופן הכתיבה, הקלט, הפלט ודוגמא:

תכולת חבילת Custom Functions התעדכנה לאחרונה וכוללת פונקציות רבות ומעניינות, בין היתר:

  • בהוספת אפסים מובילים לשדה נומרי והפיכתו לשדה טקסט cwi_LeadingZeros
  • המרת נתון בפורמט תאריך לא ולידי מסוג d-m-yyyy לשדה תאריך בפורמט המקובל ב idea  של (yyyymmdd) -  cwi_DMYYYYToIdea
  • הצגת השבוע בחודש בהתבסס על הגדרה מי היום הראשון בשבוע - cwi_WeekOfYear

להלן רשימת הפונקציות מסוג Custom Functions ולצד כל אחת תיאור קצר למהותה:

Date/Time Custom Functions

cwi_1stOfNextMonth - Finds the first of the next month based on a Date field or a Date expression

cwi_CombDateNTime - Returns a datetime that combines the date and time

cwi_CompTime - Compare two time inputs. Returns ‘1’ if the first time is larger, ‘-1’ if the second time is larger, and ‘0’ if they are the same

cwi_DateAddMonth - Returns the date that is x months from the given date

cwi_DateDelta - Returns a new date with the added years, months and days.

cwi_DateDif - Returns the number of days, months, or years between two dates

cwi_DateReplaceDay - Returns a date with all the same values except the date is updated to the new value

cwi_DateReplaceMonth - Returns a date with the same value, except change the month to the new month value

cwi_DateReplaceYear - Returns a date with the same value, except change the year to the new year value

cwi_Days - This function will take a date field and output a numeric value with no decimals of the number of days since the beginning of the year. Feb 1, 2011 would be 32 as there are 31 days in January plus the first day in February

cwi_Days360 - Returns the number of days between two dates based on a 360-day year (twelve 30-day months)

cwi_DMYYYYToIdea - Converts a string in D/M/YYYY or D-M-YYYY format to the IDEA Date format (YYYYMMDD). D can be one or two digits (1 or 01). M can be one, two or three characters or digits (01, 1, Jul). Any date prior to 19000101 is invalid.

cwi_EOMonth - Returns the date of the last day of the month, x months after the date

cwi_FirstDayOfPreviousMonth - Calculates the first day of the previous month based on a date or date field

cwi_FormatDate - Allows for the formatting of the data into many different formats using the YYYYMMDD mask

cwi_IsLeapYear - Returns True if year is a leap year; otherwise, False

cwi_ISOWeeknum - Returns the number of the ISO week number of the year for a given date

cwi_JDEJtod - Converts the JD Edwards (JDE) Julian Date format to the IDEA Date format (YYYYMMDD). The JDE Julian Date format is xyyzzz, where x is century index (century index starts at 0 for 1900), yy is decade, and ddd is the day number within the year. This custom function complies with leap years. If an invalid JDE Julian date is input, the custom function will return 19000101.

cwi_LastDayOfPreviousMonth - Calculates the last day of the previous month based on a date or date field

cwi_LeapDays - Returns the total number of leap days in the years within the two dates range

cwi_NetWorkdays - Returns the number of whole working days between Date1 and Date2

cwi_TimeAdd - Return the sum of the two input time values

cwi_TimeDelta - Return a new time with added hours, minutes, and seconds on the old time

cwi_TimeReplaceHour - Returns a time with the same value, except it changes the hour to the new hour value

cwi_TimeReplaceMin - Returns a time with the same value, except it changes the minute to the new minute value

cwi_TimeReplaceSec - Returns a time with the same value, except it changes the second to the new second value

cwi_TimeTrans - Converts 24 hour time to 12 hour time

cwi_WeekOfYear - Returns the week of the year based on the week starting date

cwi_YearFrac - Returns the fraction of the year represented by the number of whole days between two dates (the start_date and the end_date)

Character Custom Functions

cwi_CountCharacters - Returns the number of times a specified value occurs in a string. This test case is sensitive so apple and Apple would be considered as different words

cwi_EndsWith - Returns true if the string ends with the specified value. This test is case sensitive

cwi_Exclude - Returns a string that excludes the specified characters

cwi_ExtNumberID - This custom function will look for the first numeric series that fits the length indicated. If the numeric series has any spaces, the numeric series will not be used. The numeric series must be surrounded by nonnumeric characters.

cwi_Include - Returns a string that includes the specified characters

cwi_IsAlpha - Returns True if all characters in the string are in the alphabet, else returns False

cwi_IsAlphaNumeric - Returns True if all characters in the string are alphanumeric, else returns False

cwi_IsDigits - Returns True if all characters in the string are digits (0-9), else returns False

cwi_IsIdentifier - Returns True if the string is an identifier (a-z, A-Z, 0-9, or “_”), else returns False

cwi_IsLower - Returns True if all characters in the string are lower case, else returns False

cwi_IsSpace - Returns True if all characters in the string are whitespaces, else returns False

cwi_IsTitle - Returns True if the string follows the rules of a title, else returns False. The title rules are for the English language

cwi_IsUpper - Returns True if all characters in the string are uppercase, else will return False

cwi_LeadingZeros - Converts a numeric value to a string value and appends leading zeros up to a specified number of characters. In the example provided below, notice that the FieldLength parameter is specified as 10 and the Number is 123. Since 123 is three characters in length, the custom function has added seven leading zeros. This function also takes the absolute value of the number. So if the number is negative, the negative will be removed. It will keep decimals.

cwi_NumberToString - This function will extract all the numbers from a source string, in order, while observing the maximum length parameter. If the maximum length is reached within a continuous stretch of numbers within the source string, the extraction of the number stops at the next text character in the string. Extraction will continue to extract non-continuous numbers from the string until the maximum is reached. Nothing is returned if there are not enough extracted numbers to reach the maximum.

cwi_RemoveBrackets - Removes brackets () and all characters in between

cwi_ReplaceNonAlphaChar - Replaces all non alpha characters with an underscore

cwi_Rept - Repeats the text a given number of times. Use cwi_Rept to fill a cell with a number of instances of a text string

cwi_ShowFormat - This custom function will show the format of a character field. It will show numbers as a “9”, upper case letters as “X” and lowercase letters as “x”. All other characters will stay the same

cwi_SortChar - This will sort a character field alphabetically. An example would be if you have “My dog is blue” it will return “bdegilMosuy”. Note that the three spaces are at the beginning so if you don’t have the spaces you can use the @strip to remove them.

cwi_StartsWith - Returns True if the string starts with the specified value, else returns False

cwi_StringToASCII - The cwi_StringToASCII function will take a string and combine all the characters by their ASCII values to obtain an overall value for the entire string. This can be used to find duplicates in a string where the letters in the words might have been rearranged, such as “CaseWare is great” will return the same value as “is great CaseWare”. This can be used in combination with the summary or duplicates to look for strings that might contain the same letter combination but have been rearranged. This function also allows you to keep the case the same as the original, or change it all to uppercase in instances where the words might stay the same but the case could have changed.

cwi_SwapCase - Swaps cases. Lower case becomes upper case, and vice versa

cwi_TrimEnd - Will remove the defined trailing character(s) from a field. This test is case sensitive

cwi_TrimStart - Will remove the defined leading character(s) from a field. This test is case sensitive

Numeric Custom Functions

cwi_FormatNumber - Allows for the formatting of the number into many different numeric formats using a mask

cwi_Ceiling - Rounds up any numeric value to the factor or multiple desired

cwi_Floor - The Floor function will take any numeric value and round it down to the factor or multiple desired

cwi_RecurringDigits - This function searches a string to determine if there are recurring digits within

The first parameter is the string to be searched and the second is the minimum number of recurring digits to search for. The second parameter should be greater than or equal to 2. This function is best used as a Criteria to limit the rows in the Source database. If the minimum number of recurring digits is found in the string a 1 is returned, otherwise a 0.

cwi_FirstDigit - This function will extract the first digit from a numeric field. If the amount is a decimal, it will remove the decimal and use the first non 0 number in the field

 

Financial Custom Functions

cwi_Rate - Calculates the interest rate per period of a loan or an investment

 

 

הרשמה

שכחת סיסמה?