This blog shows how to use 10 functions (+3 similar functions) that I learned before going for the Alteryx Core exam. I will briefly explain how to use each of these functions and what you should watch out for when you are using them.
A word of caution: always pay attention to the data type that is expected for the input and the data type that is returned as the result.
NOTE: If the input argument of the function is option, they will be put within a square bracket in this tutorial.
1. DateTimeParse(Date-Time String, String Format, [language])
This function converts a date string with a specific format (e.g: year/month/day) to the ISO date format that used in Alteryx. We need to specify the format of the date-time string accordingly for the conversion to happen successfully.
In the following use case, '%Y/%m/%d' is used to match the date string written in such manner: '2021/01/01'.
%Y: refers to the 4 digits of the year (in this case %y can also)
%m: refers to the 2 digits of the month
%d: refers to the 2 digits of the day
2. DateTimeFormat(Date-Time Data, String Format, [language])
The most common use case is to use this function to get the day of the week from date.
NOTE: It requires the date to be in Date/DateTime type, Date as String field will not work!
3. DateTimeDiff(Date-Time Data 1, Date-Time Data 2, Date-Time unit as String)
Calculate the difference between two Date-Time Data, basically subtract the second argument (Date-Time Data 2) from the first argument (Date-Time Data 1). Date-Time unit should be expressed as string in plural: (e.g: years, months, days, hours, minutes, or seconds.)
NOTE: The result is an integer so if the difference is less than 1, it will return as integer even if we change the Data type to double in the formula. For example if the difference in years is less than 1, you will get 0 instead of the actual decimal.
4. DateTimeAdd(Date-Time Data, Positive/Negative Integer, Date-Time Unit)
We can use this function to add or subtract a date-time data by Date-Time Unit (years, months, days, hours, minutes, or seconds) expressed in string.
NOTE: Cautious with the data type of the first argument. Make sure it is a date/datetime format, if it is a date time string, it will give an error.
5. Trim(Input string, String to be trimmed)
I want to highlight a cool use of this function. If we omit the second argument, the function will just trim the leading spaces and trailing spaces of the target string as shown in the example below.
6. StartsWith(Input String, String to be trimmed, [CaseInsensitive=1])
A common use case in some of the Alteryx challenges/ exam question would be filtering out weekend/weekday from the dataset. Another simple use case could be filtering out people with certain first name. (Could use EndsWith() function for last name)
NOTE: The return result is True/False (Boolean data type). The default value of the three argument is 1 so it is NOT case sensitive.
7. Contains(Input String, String to be matched, [CaseInsensitive=1])
As its name suggests, this function will check if a particular string (String to be matched) exists within another string (Input String).
NOTE: Same as the function above. The return result is True/False (Boolean data type). The default value of the three argument is 1 so it is NOT case sensitive.
8. Left(Input String, Length)/Right(Input String, Length)
These two functions are useful if we want to extract certain parts of a string field. As the name suggested, "Left" function extract the substring from the input string from the left according to the length input argument. Same logic applies for the right function.
9. Length(Input String)
A pretty straightforward function. It returns the length of the string as a numeric data type.
10. PadLeft(Input String, Length, Char to be padded)/PadRight(Input String, Length, Char to be padded)
Pad the input string with specified character to be padded to the length input from the left if it is PadLeft(); from the right if it is PadRight()
NOTE: The result is a String data type, if you are padding a number, make sure it is a string type instead of other numeric data type.
