Page tree

Trifacta Dataprep



Contents:

If you licensed Dataprep by Trifacta before Oct. 14, 2020, you are using the Dataprep by Trifacta Legacy product edition. On October 14, 2022, this product edition will be decommissioned by Google and will be no longer available for use. Current customers of this product edition are encouraged to transition to one of the product editions hosted by Trifacta. See Product Editions.

   

This example illustrates how you can apply functions to derive day-of-week values out of a column of Datetime type.

Functions:

ItemDescription
WEEKDAY Function Derives the numeric value for the day of the week (1, 2, etc.). Input must be a reference to a column containing Datetime values.
WEEKNUM Function Derives the numeric value for the week within the year (1, 2, etc.). Input must be the output of the DATE function or a reference to a column containing Datetime values. The output of this function increments on Sunday.
DATEFORMAT Function Formats a specified Datetime set of values according to the specified date format. Source values can be a reference to a column containing Datetime values.

Source:

myDate
10/30/17
10/31/17
11/1/17
11/2/17
11/3/17
11/4/17
11/5/17
11/6/17


Transformation:

The following transformation step generates a numeric value for the day of week in a new column:

Transformation Name New formula
Parameter: Formula type Single row formula
Parameter: Formula WEEKDAY (myDate)
Parameter: New column name 'weekDayNum'

The following step generates a full text version of the name of the day of the week:

Transformation Name New formula
Parameter: Formula type Single row formula
Parameter: Formula DATEFORMAT(myDate, 'EEEE')
Parameter: New column name 'weekDayNameFull'

The following step generates a three-letter abbreviation for the name of the day of the week:

Transformation Name New formula
Parameter: Formula type Single row formula
Parameter: Formula DATEFORMAT(myDate, 'EEE')
Parameter: New column name 'weekDayNameShort'

The following step generates the numeric value of the week within the year:

Transformation Name New formula
Parameter: Formula type Single row formula
Parameter: Formula WEEKNUM (myDate)
Parameter: New column name 'weekNum'

Results:

myDateweekDayNumweekDayNameFullweekDayNameShortweekNum
10/30/171MondayMon44
10/31/172TuesdayTue44
11/1/173WednesdayWed44
11/2/174ThursdayThu44
11/3/175FridayFri44
11/4/176SaturdaySat44
11/5/177SundaySun45
11/6/171MondayMon45

See Also for EXAMPLE - Day of Functions:

This page has no comments.