Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Published by Scroll Versions from space DEV and version r0821

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

  • WEEKDAY - returns numeric value for the day of the week for source Datetime values. See WEEKDAY Function
  • WEEKNUM - returns the numeric value for the week within the year for source Datetime values. See WEEKNUM Function.
  • DATEFORMAT - can be used to format Datetime values in many different ways. See DATEFORMAT Function.

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:

D trans
RawWrangletrue
p03Value'weekDayNum'
Typestep
WrangleTextderive type:single value: WEEKDAY (myDate) as:'weekDayNum'
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02ValueWEEKDAY (myDate)
p03NameNew column name
SearchTermNew formula

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

D trans
RawWrangletrue
p03Value'weekDayNameFull'
Typestep
WrangleTextderive type:single value:DATEFORMAT(myDate, 'EEEE') as:'weekDayNameFull'
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02ValueDATEFORMAT(myDate, 'EEEE')
p03NameNew column name
SearchTermNew formula

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

D trans
RawWrangletrue
p03Value'weekDayNameShort'
Typestep
WrangleTextderive type:single value:DATEFORMAT(myDate, 'EEE') as:'weekDayNameShort'
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02ValueDATEFORMAT(myDate, 'EEE')
p03NameNew column name
SearchTermNew formula

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

D trans
RawWrangletrue
p03Value'weekNum'
Typestep
WrangleTextderive type:single value: WEEKNUM (myDate) as:'weekNum'
p01NameFormula type
p01ValueSingle row formula
p02NameFormula
p02ValueWEEKNUM (myDate)
p03NameNew column name
SearchTermNew formula

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