Test 2 - Calculations Flashcards
What is the DATEDIFF syntax?
DATEDIFF(‘date_part’, date1, date2)
What does the DATE function do?
Changes string and number expressions into dates, as long as they are in a recognizable format
What function should you use if DATE does not recognize the input?
DATEPARSE
What does DATENAME do?
Returns the requested date part of a given date as a string
What is the DATENAME syntax?
DATENAME(‘date_part’, date)
What does DATEPARSE do?
returns specifically formatted strings as dates
What is the DATEPARSE syntax?
DATEPARSE(‘date_format’, “date_string”)
What does DATEPART do?
returns the name of the specified date part as an integer
What is the syntax for DATEPART?
DATEPART(‘date_part’, date)
What does MAKEDATE do?
returns a date value constructed from the specified year, month, and day
What is the syntax for MAKEDATE?
MAKEDATE(year, month, day)
What is the syntax for CONTAINS
CONTAINS(“string”, “substring”)
What is the syntax for LEFT?
LEFT(“string”, “number of characters”)
What are three functions used to change casing?
LOWER, UPPER, PROPER
What do LTRIM, RTRIM, and TRIM do?
Remove spacing from the respective sides
What does the ZN function do?
Returns value if given expression isn’t null, or 0 if it is null
What is the syntax for a type conversion function?
TYPE(expression)
What is the syntax for REPLACE?
REPLACE(string, substring, replacement)
What is the syntax for SPLIT?
SPLIT(string, delimiter, token number)
What is the result of SPLIT (“a-b-c-d”, “-“, 2)?
b
What is the result of SPLIT (“a|b|c|d”, “|”, -2)?
c
What is CASE statement syntax?
CASE <expression>
WHEN <'value1'> THEN <'then1'>
WHEN <'value2'> THEN <'then2'>
...
[ELSE <'default'>]
END</expression>
What does IF NULL do?
Given syntax IFNULL(expr1, expr2), returns expr1 if non-null, otherwise returns expr2
What do FIXED LOD calculations do?
compute a value using the specified dimensions, without reference to the dimensions in the view
What are the Quick Table calculations available?
- Running total
- Difference
- Percent difference
- Percent of total
- Rank
- Percentile
- Moving average
- YTD total
- Compound growth rate
- Year of year growth
- YTD growth
Which table calculation returns the number of rows from the current row to the first row in the partition?
FIRST()
Which table calculation returns the index of the current row in the partition, without any sorting with regard to value?
INDEX()
Which table calculation returns the value of the expression in a target row, specified as a relative offset from the current row?
LOOKUP()
Which table calculation might return the following rankings : 1,1,1,4,4,6,7,7,7,10?
RANK()
Which table calculation might return the following rankings : 1,1,1,2,2,3,4,4,4,5?
RANK_DENSE()
Which table calculation might return the following rankings : 3,3,3,5,5,6,9,9,9,10?
RANK_MODIFIED()
Which table calculation might return the following rankings : 1,2,3,4,5,6,7,8,9,10?
RANK_UNIQUE()
Which table calculation returns rankings as percentiles?
RANK_PERCENTILE()
