Common Function FAQ
How do I correctly handle incorrect week display across years?
Scenario: In a dataset or ETL, you use concat(year([Date]),' Year Week ',weekofyear([Date]),' ')
to get the week for a date. For a cross-year week, such as 2021-01-01, the actual week is Week 53 of 2020, but the result becomes Week 53 of 2021.
Solution: Use the date of the Thursday in the current week to obtain the year for the cross-year week. Spark numbers weeks according to ISO 8601. If the week containing January 1, counted from Monday, has more than three days in the new year, it is Week 1 of the new year. Otherwise, it is the last week of the previous year. Therefore, the year of the Thursday in a cross-year week is the year to which the week belongs. Use this syntax:
concat(year(date_add(trunc([Date],'week'),3)),'Year Week ',weekofyear([Date]))
In card editing scenarios, use the built-in Date - Week field directly.
How do I convert a string timestamp to timestamp format?
For example, the database stores time as the string 20200905174444, and you want to convert it to a timestamp such as 2020-09-05 17:44:44.
Solution: First convert the field to timestamp format, and then use DATE_FORMAT to convert it to the target format.
DATE_FORMAT(CAST(UNIX_TIMESTAMP([Date], 'yyyyMMddHHmmss') AS TIMESTAMP), 'yyyy-MM-dd HH:mm:ss')
Notes:
-
The function above uses Spark syntax and applies to non-direct datasets. Direct datasets must use the syntax of the corresponding database. For example, Oracle syntax is: to_date([Date],'yyyyMMddhh24:mi:ss').
-
Select Date and Time as the format. If you select Date, hours, minutes, and seconds are not displayed.
-
If the original format is numeric, convert the field to string format first.
Why does decoding with unbase64 not take effect?
Cause: unbase64 returns binary data. You must use cast to convert it to the string type.
Solution: Use cast(unbase64([Encrypted String]) as string).
Why does the power() function return 0 for all results?
Cause: This is a limitation of the POW(x,y) function. When x<0 and y<1, that is, when y is a fraction or decimal, the result is 0. In addition, if base x is negative and exponent y is not an integer, a domain error occurs. You can use a case when function to work around the issue: case when x<0 then -POW(ABS(x),y) else POW(x,y) end
Why does the sequence function report an error?
For example:
Possible causes:
-
The start date and end date may be reversed. The correct syntax is: sequence([Start Date],[End Date],interval 1 month).
-
The step size of the third parameter may be negative. It should be positive.
-
The source data may contain dirty data, such as a start date later than the end date. Filter out this data first.
How do I use the now function to get yesterday's date and time with hours, minutes, and seconds?
from_unixtime(to_unix_timestamp(now()) - 86400, 'yyyy-MM-dd HH:mm:ss')
Why does SUBSTRING fail to extract from the end when the start position is negative?
For example, substring([Store Name],-1,4) cannot extract the last four characters from the end.
If the data source is SQL Server, use this syntax as a reference: substring([Store Name],Len([Store Name])-1,4). Built-in functions may not be universal for direct databases. Use the syntax of the data source itself.
How do I get the latest time in a time column?
max([Time Field])over(partition by [Field])
To get the maximum value across all data, write: max([Time Field])over(partition by null).
After using round() to round a value, why does it still include one decimal place? How do I remove it?
Nest an int function outside it to convert the value to an integer: int(round()).
What may cause calculation errors in a newly created field?
If a calculation contains null values, the result is null. Add a null value replacement step before ETL output.
How do I convert a string date to date format when to_date() directly returns null?
If the original format is 12/25/19 (month/day/year), write to_date([Date],"MM/dd/yy). Specify the original format after to_date to convert it correctly.
Why do row counts become incorrect when using dense_rank to get the top rankings?
For example, if(dense_rank () over (partition by null order by[Amount ]desc)>8,' Others ',[Country]) may return more than 8 countries.
Cause: dense_rank returns rankings such as 12234455667788, so values greater than 8 may involve more than 8 rows or categories.
Solution: Use row_number() over( partition by [Field] order by [Field]).
How do I calculate week numbers by treating January 1 as the first day of the first week of the year, based on the Oracle standard week?
Solution: In addition to the internationally used ISO week calculation method, Oracle supports another standard week counting method. January 1 is the start of the first week, and date+6 days is the end of the week. Use this function:
to_char(date,'ww')
For other databases, use existing date functions to calculate which day of the year the specified date is, divide it by 7, and round up. Common functions for getting the position of a specified date within a year include the following. Spark supports all of these functions, but usage may vary slightly across databases.
dayofyear(date)
date_format(date,'D')
date_part('doy', date)
extract(doy FROM date)
For numeric calculations, ceiling/ceil is commonly used to round up. Use it as shown below, or use the remainder to determine the week number.
Method 1: ceil(dayofyear(date)/7)
Method 2:
Syntax 1: if(date_format(date,'D')%7=0,cast((date_format(date,'D')/7) as int),cast((date_format(date,'D')/7) as int)+1);
Syntax 2: case when cast(EXTRACT(DOY FROM date) as int)%7 = 0
then cast((cast(EXTRACT(DOY FROM date) as int)/7) as int)
else cast((cast(EXTRACT(DOY FROM date) as int)/7) as int)+1
end